﻿
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'RethrowError')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')
END
GO

ALTER PROCEDURE RethrowError AS
    /* Return if there is no error information to retrieve. */
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200); 

    /* Assign variables to error-handling functions that
       capture information for RAISERROR. */

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); 

    /* Building the message string that will contain original
       error information. */

    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
         'Message: '+ ERROR_MESSAGE(); 

    /* Raise an error: msg_str parameter of RAISERROR will contain
	   the original error information. */

    RAISERROR(@ErrorMessage, @ErrorSeverity, 1,
        @ErrorNumber,    /* parameter: original error number. */
        @ErrorSeverity,  /* parameter: original error severity. */
        @ErrorState,     /* parameter: original error state. */
        @ErrorProcedure, /* parameter: original error procedure name. */
        @ErrorLine       /* parameter: original error line number. */
        );

GO

----------------------------------------------------------------
-- [dbo].[AlquilerVehiculo] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertAlquilerVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertAlquilerVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertAlquilerVehiculo]
    @cantidad int,
	@comentarios nvarchar(150) = NULL,
	@fecha datetime,
	@idBien int,
	@idProveedor int,
	@idVehiculoAlquilado int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[AlquilerVehiculo] ([Cantidad], [Comentarios], [Fecha], [IdBien], [IdProveedor], [IdVehiculoAlquilado])
	VALUES (@cantidad, @comentarios, @fecha, @idBien, @idProveedor, @idVehiculoAlquilado)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateAlquilerVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateAlquilerVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateAlquilerVehiculo]
    @cantidad int,
	@comentarios nvarchar(150) = NULL,
	@fecha datetime,
	@idBien int,
	@idProveedor int,
	@idVehiculoAlquilado int
AS
BEGIN

	--The [dbo].[AlquilerVehiculo] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[AlquilerVehiculo] 
	SET [Cantidad] = @cantidad, [Comentarios] = @comentarios, [Fecha] = @fecha
	WHERE [IdBien]=@idBien AND [IdProveedor]=@idProveedor AND [IdVehiculoAlquilado]=@idVehiculoAlquilado

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteAlquilerVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteAlquilerVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteAlquilerVehiculo]
	 @idBien int,
	@idProveedor int,
	@idVehiculoAlquilado int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[AlquilerVehiculo]
	WHERE [IdBien]=@idBien AND [IdProveedor]=@idProveedor AND [IdVehiculoAlquilado]=@idVehiculoAlquilado
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromAlquilerVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromAlquilerVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromAlquilerVehiculo]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[alquilerVehiculo].[Cantidad] AS 'Cantidad',
	[alquilerVehiculo].[Comentarios] AS 'Comentarios',
	[alquilerVehiculo].[Fecha] AS 'Fecha',
	[alquilerVehiculo].[IdBien] AS 'IdBien',
	[alquilerVehiculo].[IdProveedor] AS 'IdProveedor',
	[alquilerVehiculo].[IdVehiculoAlquilado] AS 'IdVehiculoAlquilado'
FROM [dbo].[AlquilerVehiculo] [alquilerVehiculo]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAlquilerVehiculoByIdBienAndByIdProveedorAndByIdVehiculoAlquilado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAlquilerVehiculoByIdBienAndByIdProveedorAndByIdVehiculoAlquilado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAlquilerVehiculoByIdBienAndByIdProveedorAndByIdVehiculoAlquilado] 
	@idBien int,
	@idProveedor int,
	@idVehiculoAlquilado int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[alquilerVehiculo].[Cantidad] AS 'Cantidad',
	[alquilerVehiculo].[Comentarios] AS 'Comentarios',
	[alquilerVehiculo].[Fecha] AS 'Fecha',
	[alquilerVehiculo].[IdBien] AS 'IdBien',
	[alquilerVehiculo].[IdProveedor] AS 'IdProveedor',
	[alquilerVehiculo].[IdVehiculoAlquilado] AS 'IdVehiculoAlquilado'
	FROM [dbo].[AlquilerVehiculo] [alquilerVehiculo]
	WHERE [IdBien]=@idBien AND [IdProveedor]=@idProveedor AND [IdVehiculoAlquilado]=@idVehiculoAlquilado

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Area] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertArea')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertArea] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertArea]
    @descripcion nvarchar(200) = NULL,
	@idArea int OUT,
	@idAreaSuperior int,
	@idSucursal int,
	@nombre nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Area] ([Descripcion], [IdAreaSuperior], [IdSucursal], [Nombre])
	VALUES (@descripcion, @idAreaSuperior, @idSucursal, @nombre)
    SET @idArea = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateArea')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateArea] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateArea]
    @descripcion nvarchar(200) = NULL,
	@idArea int,
	@idAreaSuperior int,
	@idSucursal int,
	@nombre nvarchar(50)
AS
BEGIN

	--The [dbo].[Area] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Area] 
	SET [Descripcion] = @descripcion, [IdAreaSuperior] = @idAreaSuperior, [IdSucursal] = @idSucursal, [Nombre] = @nombre
	WHERE [IdArea]=@idArea

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteArea')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteArea] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteArea]
	 @idArea int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Area]
	WHERE [IdArea]=@idArea
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromArea')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromArea] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromArea]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[area].[Descripcion] AS 'Descripcion',
	[area].[IdArea] AS 'IdArea',
	[area].[IdAreaSuperior] AS 'IdAreaSuperior',
	[area].[IdSucursal] AS 'IdSucursal',
	[area].[Nombre] AS 'Nombre'
FROM [dbo].[Area] [area]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAreaByIdArea')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAreaByIdArea] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAreaByIdArea] 
	@idArea int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[area].[Descripcion] AS 'Descripcion',
	[area].[IdArea] AS 'IdArea',
	[area].[IdAreaSuperior] AS 'IdAreaSuperior',
	[area].[IdSucursal] AS 'IdSucursal',
	[area].[Nombre] AS 'Nombre'
	FROM [dbo].[Area] [area]
	WHERE [IdArea]=@idArea

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAreaByIdAreaSuperiorAndByIdSucursal')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAreaByIdAreaSuperiorAndByIdSucursal] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAreaByIdAreaSuperiorAndByIdSucursal] 
	@idAreaSuperior int,
	@idSucursal int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[area].[Descripcion] AS 'Descripcion',
	[area].[IdArea] AS 'IdArea',
	[area].[IdAreaSuperior] AS 'IdAreaSuperior',
	[area].[IdSucursal] AS 'IdSucursal',
	[area].[Nombre] AS 'Nombre'
	FROM [dbo].[Area] [area]
	WHERE [IdAreaSuperior]=@idAreaSuperior AND [IdSucursal]=@idSucursal

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Asignacion] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertAsignacion]
    @descripcion nvarchar(150) = NULL,
	@fecha datetime,
	@idAsignacion int OUT,
	@idEmpleado smallint,
	@idNotaSalida int,
	@tipo int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Asignacion] ([Descripcion], [Fecha], [IdEmpleado], [IdNotaSalida], [Tipo])
	VALUES (@descripcion, @fecha, @idEmpleado, @idNotaSalida, @tipo)
    SET @idAsignacion = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateAsignacion]
    @descripcion nvarchar(150) = NULL,
	@fecha datetime,
	@idAsignacion int,
	@idEmpleado smallint,
	@idNotaSalida int,
	@tipo int
AS
BEGIN

	--The [dbo].[Asignacion] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Asignacion] 
	SET [Descripcion] = @descripcion, [Fecha] = @fecha, [IdEmpleado] = @idEmpleado, [IdNotaSalida] = @idNotaSalida, [Tipo] = @tipo
	WHERE [IdAsignacion]=@idAsignacion

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteAsignacion]
	 @idAsignacion int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Asignacion]
	WHERE [IdAsignacion]=@idAsignacion
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromAsignacion]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[asignacion].[Descripcion] AS 'Descripcion',
	[asignacion].[Fecha] AS 'Fecha',
	[asignacion].[IdAsignacion] AS 'IdAsignacion',
	[asignacion].[IdEmpleado] AS 'IdEmpleado',
	[asignacion].[IdNotaSalida] AS 'IdNotaSalida',
	[asignacion].[Tipo] AS 'Tipo'
FROM [dbo].[Asignacion] [asignacion]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAsignacionByIdAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAsignacionByIdAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAsignacionByIdAsignacion] 
	@idAsignacion int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[asignacion].[Descripcion] AS 'Descripcion',
	[asignacion].[Fecha] AS 'Fecha',
	[asignacion].[IdAsignacion] AS 'IdAsignacion',
	[asignacion].[IdEmpleado] AS 'IdEmpleado',
	[asignacion].[IdNotaSalida] AS 'IdNotaSalida',
	[asignacion].[Tipo] AS 'Tipo'
	FROM [dbo].[Asignacion] [asignacion]
	WHERE [IdAsignacion]=@idAsignacion

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAsignacionByIdEmpleadoAndByIdNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAsignacionByIdEmpleadoAndByIdNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAsignacionByIdEmpleadoAndByIdNotaSalida] 
	@idEmpleado smallint,
	@idNotaSalida int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[asignacion].[Descripcion] AS 'Descripcion',
	[asignacion].[Fecha] AS 'Fecha',
	[asignacion].[IdAsignacion] AS 'IdAsignacion',
	[asignacion].[IdEmpleado] AS 'IdEmpleado',
	[asignacion].[IdNotaSalida] AS 'IdNotaSalida',
	[asignacion].[Tipo] AS 'Tipo'
	FROM [dbo].[Asignacion] [asignacion]
	WHERE [IdEmpleado]=@idEmpleado AND [IdNotaSalida]=@idNotaSalida

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Bien] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertBien]
    @descripcion nvarchar(250) = NULL,
	@estado int,
	@idBien int OUT,
	@idUnidadMedida int,
	@nombre nvarchar(50),
	@precio money,
	@stockActual decimal(8,4),
	@stockMinimo decimal(8,4) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Bien] ([Descripcion], [Estado], [IdUnidadMedida], [Nombre], [Precio], [StockActual], [StockMinimo])
	VALUES (@descripcion, @estado, @idUnidadMedida, @nombre, @precio, @stockActual, @stockMinimo)
    SET @idBien = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateBien]
    @descripcion nvarchar(250) = NULL,
	@estado int,
	@idBien int,
	@idUnidadMedida int,
	@nombre nvarchar(50),
	@precio money,
	@stockActual decimal(8,4),
	@stockMinimo decimal(8,4) = NULL
AS
BEGIN

	--The [dbo].[Bien] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Bien] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [IdUnidadMedida] = @idUnidadMedida, [Nombre] = @nombre, [Precio] = @precio, [StockActual] = @stockActual, [StockMinimo] = @stockMinimo
	WHERE [IdBien]=@idBien

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteBien]
	 @idBien int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Bien]
	WHERE [IdBien]=@idBien
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromBien]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[bien].[Descripcion] AS 'Descripcion',
	[bien].[Estado] AS 'Estado',
	[bien].[IdBien] AS 'IdBien',
	[bien].[IdUnidadMedida] AS 'IdUnidadMedida',
	[bien].[Nombre] AS 'Nombre',
	[bien].[Precio] AS 'Precio',
	[bien].[StockActual] AS 'StockActual',
	[bien].[StockMinimo] AS 'StockMinimo'
FROM [dbo].[Bien] [bien]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetBienByIdBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetBienByIdBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetBienByIdBien] 
	@idBien int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[bien].[Descripcion] AS 'Descripcion',
	[bien].[Estado] AS 'Estado',
	[bien].[IdBien] AS 'IdBien',
	[bien].[IdUnidadMedida] AS 'IdUnidadMedida',
	[bien].[Nombre] AS 'Nombre',
	[bien].[Precio] AS 'Precio',
	[bien].[StockActual] AS 'StockActual',
	[bien].[StockMinimo] AS 'StockMinimo'
	FROM [dbo].[Bien] [bien]
	WHERE [IdBien]=@idBien

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetBienByIdUnidadMedida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetBienByIdUnidadMedida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetBienByIdUnidadMedida] 
	@idUnidadMedida int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[bien].[Descripcion] AS 'Descripcion',
	[bien].[Estado] AS 'Estado',
	[bien].[IdBien] AS 'IdBien',
	[bien].[IdUnidadMedida] AS 'IdUnidadMedida',
	[bien].[Nombre] AS 'Nombre',
	[bien].[Precio] AS 'Precio',
	[bien].[StockActual] AS 'StockActual',
	[bien].[StockMinimo] AS 'StockMinimo'
	FROM [dbo].[Bien] [bien]
	WHERE [IdUnidadMedida]=@idUnidadMedida

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[BienPatrimonial] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertBienPatrimonial')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertBienPatrimonial] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertBienPatrimonial]
    @idBien int,
	@tipoBien int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[BienPatrimonial] ([IdBien], [TipoBien])
	VALUES (@idBien, @tipoBien)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateBienPatrimonial')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateBienPatrimonial] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateBienPatrimonial]
    @idBien int,
	@tipoBien int
AS
BEGIN

	--The [dbo].[BienPatrimonial] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[BienPatrimonial] 
	SET [TipoBien] = @tipoBien
	WHERE [IdBien]=@idBien

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteBienPatrimonial')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteBienPatrimonial] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteBienPatrimonial]
	 @idBien int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[BienPatrimonial]
	WHERE [IdBien]=@idBien
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromBienPatrimonial')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromBienPatrimonial] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromBienPatrimonial]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[bienPatrimonial].[IdBien] AS 'IdBien',
	[bienPatrimonial].[TipoBien] AS 'TipoBien'
FROM [dbo].[BienPatrimonial] [bienPatrimonial]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetBienPatrimonialByIdBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetBienPatrimonialByIdBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetBienPatrimonialByIdBien] 
	@idBien int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[bienPatrimonial].[IdBien] AS 'IdBien',
	[bienPatrimonial].[TipoBien] AS 'TipoBien'
	FROM [dbo].[BienPatrimonial] [bienPatrimonial]
	WHERE [IdBien]=@idBien

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[BienPatrimonial] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Cargo] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertCargo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertCargo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertCargo]
    @descripcion nvarchar(200) = NULL,
	@idArea int,
	@idCargo int OUT,
	@idCargoSuperior int,
	@nombre nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Cargo] ([Descripcion], [IdArea], [IdCargoSuperior], [Nombre])
	VALUES (@descripcion, @idArea, @idCargoSuperior, @nombre)
    SET @idCargo = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateCargo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateCargo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateCargo]
    @descripcion nvarchar(200) = NULL,
	@idArea int,
	@idCargo int,
	@idCargoSuperior int,
	@nombre nvarchar(50)
AS
BEGIN

	--The [dbo].[Cargo] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Cargo] 
	SET [Descripcion] = @descripcion, [IdArea] = @idArea, [IdCargoSuperior] = @idCargoSuperior, [Nombre] = @nombre
	WHERE [IdCargo]=@idCargo

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteCargo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteCargo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteCargo]
	 @idCargo int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Cargo]
	WHERE [IdCargo]=@idCargo
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromCargo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromCargo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromCargo]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[cargo].[Descripcion] AS 'Descripcion',
	[cargo].[IdArea] AS 'IdArea',
	[cargo].[IdCargo] AS 'IdCargo',
	[cargo].[IdCargoSuperior] AS 'IdCargoSuperior',
	[cargo].[Nombre] AS 'Nombre'
FROM [dbo].[Cargo] [cargo]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetCargoByIdCargo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetCargoByIdCargo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetCargoByIdCargo] 
	@idCargo int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[cargo].[Descripcion] AS 'Descripcion',
	[cargo].[IdArea] AS 'IdArea',
	[cargo].[IdCargo] AS 'IdCargo',
	[cargo].[IdCargoSuperior] AS 'IdCargoSuperior',
	[cargo].[Nombre] AS 'Nombre'
	FROM [dbo].[Cargo] [cargo]
	WHERE [IdCargo]=@idCargo

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetCargoByIdAreaAndByIdCargoSuperior')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetCargoByIdAreaAndByIdCargoSuperior] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetCargoByIdAreaAndByIdCargoSuperior] 
	@idArea int,
	@idCargoSuperior int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[cargo].[Descripcion] AS 'Descripcion',
	[cargo].[IdArea] AS 'IdArea',
	[cargo].[IdCargo] AS 'IdCargo',
	[cargo].[IdCargoSuperior] AS 'IdCargoSuperior',
	[cargo].[Nombre] AS 'Nombre'
	FROM [dbo].[Cargo] [cargo]
	WHERE [IdArea]=@idArea AND [IdCargoSuperior]=@idCargoSuperior

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Cliente] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertCliente')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertCliente] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertCliente]
    @direccion nvarchar(150) = NULL,
	@email nvarchar(50) = NULL,
	@idCliente int OUT,
	@telefono nvarchar(20) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Cliente] ([Direccion], [Email], [Telefono])
	VALUES (@direccion, @email, @telefono)
    SET @idCliente = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateCliente')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateCliente] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateCliente]
    @direccion nvarchar(150) = NULL,
	@email nvarchar(50) = NULL,
	@idCliente int,
	@telefono nvarchar(20) = NULL
AS
BEGIN

	--The [dbo].[Cliente] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Cliente] 
	SET [Direccion] = @direccion, [Email] = @email, [Telefono] = @telefono
	WHERE [IdCliente]=@idCliente

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteCliente')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteCliente] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteCliente]
	 @idCliente int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Cliente]
	WHERE [IdCliente]=@idCliente
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromCliente')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromCliente] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromCliente]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[cliente].[Direccion] AS 'Direccion',
	[cliente].[Email] AS 'Email',
	[cliente].[IdCliente] AS 'IdCliente',
	[cliente].[Telefono] AS 'Telefono'
FROM [dbo].[Cliente] [cliente]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetClienteByIdCliente')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetClienteByIdCliente] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetClienteByIdCliente] 
	@idCliente int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[cliente].[Direccion] AS 'Direccion',
	[cliente].[Email] AS 'Email',
	[cliente].[IdCliente] AS 'IdCliente',
	[cliente].[Telefono] AS 'Telefono'
	FROM [dbo].[Cliente] [cliente]
	WHERE [IdCliente]=@idCliente

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Cliente] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleAsignacion] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleAsignacion]
    @cantidad decimal(8,4),
	@comentario nvarchar(100) = NULL,
	@idAsignacion int,
	@idBien int,
	@idEmpleado smallint
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleAsignacion] ([Cantidad], [Comentario], [IdAsignacion], [IdBien], [IdEmpleado])
	VALUES (@cantidad, @comentario, @idAsignacion, @idBien, @idEmpleado)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleAsignacion]
    @cantidad decimal(8,4),
	@comentario nvarchar(100) = NULL,
	@idAsignacion int,
	@idBien int,
	@idEmpleado smallint
AS
BEGIN

	--The [dbo].[DetalleAsignacion] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleAsignacion] 
	SET [Cantidad] = @cantidad, [Comentario] = @comentario
	WHERE [IdAsignacion]=@idAsignacion AND [IdBien]=@idBien AND [IdEmpleado]=@idEmpleado

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleAsignacion]
	 @idAsignacion int,
	@idBien int,
	@idEmpleado smallint
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleAsignacion]
	WHERE [IdAsignacion]=@idAsignacion AND [IdBien]=@idBien AND [IdEmpleado]=@idEmpleado
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleAsignacion')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleAsignacion] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleAsignacion]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleAsignacion].[Cantidad] AS 'Cantidad',
	[detalleAsignacion].[Comentario] AS 'Comentario',
	[detalleAsignacion].[IdAsignacion] AS 'IdAsignacion',
	[detalleAsignacion].[IdBien] AS 'IdBien',
	[detalleAsignacion].[IdEmpleado] AS 'IdEmpleado'
FROM [dbo].[DetalleAsignacion] [detalleAsignacion]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleAsignacionByIdAsignacionAndByIdBienAndByIdEmpleado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleAsignacionByIdAsignacionAndByIdBienAndByIdEmpleado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleAsignacionByIdAsignacionAndByIdBienAndByIdEmpleado] 
	@idAsignacion int,
	@idBien int,
	@idEmpleado smallint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleAsignacion].[Cantidad] AS 'Cantidad',
	[detalleAsignacion].[Comentario] AS 'Comentario',
	[detalleAsignacion].[IdAsignacion] AS 'IdAsignacion',
	[detalleAsignacion].[IdBien] AS 'IdBien',
	[detalleAsignacion].[IdEmpleado] AS 'IdEmpleado'
	FROM [dbo].[DetalleAsignacion] [detalleAsignacion]
	WHERE [IdAsignacion]=@idAsignacion AND [IdBien]=@idBien AND [IdEmpleado]=@idEmpleado

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleAsignacion] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleFactura] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleFactura]
    @cantidad decimal(8,4),
	@idBien int,
	@idFactura int,
	@precio money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleFactura] ([Cantidad], [IdBien], [IdFactura], [Precio])
	VALUES (@cantidad, @idBien, @idFactura, @precio)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleFactura]
    @cantidad decimal(8,4),
	@idBien int,
	@idFactura int,
	@precio money
AS
BEGIN

	--The [dbo].[DetalleFactura] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleFactura] 
	SET [Cantidad] = @cantidad, [Precio] = @precio
	WHERE [IdBien]=@idBien AND [IdFactura]=@idFactura

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleFactura]
	 @idBien int,
	@idFactura int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleFactura]
	WHERE [IdBien]=@idBien AND [IdFactura]=@idFactura
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleFactura]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleFactura].[Cantidad] AS 'Cantidad',
	[detalleFactura].[IdBien] AS 'IdBien',
	[detalleFactura].[IdFactura] AS 'IdFactura',
	[detalleFactura].[Precio] AS 'Precio'
FROM [dbo].[DetalleFactura] [detalleFactura]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleFacturaByIdBienAndByIdFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleFacturaByIdBienAndByIdFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleFacturaByIdBienAndByIdFactura] 
	@idBien int,
	@idFactura int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleFactura].[Cantidad] AS 'Cantidad',
	[detalleFactura].[IdBien] AS 'IdBien',
	[detalleFactura].[IdFactura] AS 'IdFactura',
	[detalleFactura].[Precio] AS 'Precio'
	FROM [dbo].[DetalleFactura] [detalleFactura]
	WHERE [IdBien]=@idBien AND [IdFactura]=@idFactura

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleFactura] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleGuiaRemision] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleGuiaRemision]
    @cantidad decimal(8,4),
	@idBien int,
	@idGuiaRemision int,
	@precio money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleGuiaRemision] ([Cantidad], [IdBien], [IdGuiaRemision], [Precio])
	VALUES (@cantidad, @idBien, @idGuiaRemision, @precio)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleGuiaRemision]
    @cantidad decimal(8,4),
	@idBien int,
	@idGuiaRemision int,
	@precio money
AS
BEGIN

	--The [dbo].[DetalleGuiaRemision] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleGuiaRemision] 
	SET [Cantidad] = @cantidad, [Precio] = @precio
	WHERE [IdBien]=@idBien AND [IdGuiaRemision]=@idGuiaRemision

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleGuiaRemision]
	 @idBien int,
	@idGuiaRemision int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleGuiaRemision]
	WHERE [IdBien]=@idBien AND [IdGuiaRemision]=@idGuiaRemision
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleGuiaRemision]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleGuiaRemision].[Cantidad] AS 'Cantidad',
	[detalleGuiaRemision].[IdBien] AS 'IdBien',
	[detalleGuiaRemision].[IdGuiaRemision] AS 'IdGuiaRemision',
	[detalleGuiaRemision].[Precio] AS 'Precio'
FROM [dbo].[DetalleGuiaRemision] [detalleGuiaRemision]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleGuiaRemisionByIdBienAndByIdGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleGuiaRemisionByIdBienAndByIdGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleGuiaRemisionByIdBienAndByIdGuiaRemision] 
	@idBien int,
	@idGuiaRemision int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleGuiaRemision].[Cantidad] AS 'Cantidad',
	[detalleGuiaRemision].[IdBien] AS 'IdBien',
	[detalleGuiaRemision].[IdGuiaRemision] AS 'IdGuiaRemision',
	[detalleGuiaRemision].[Precio] AS 'Precio'
	FROM [dbo].[DetalleGuiaRemision] [detalleGuiaRemision]
	WHERE [IdBien]=@idBien AND [IdGuiaRemision]=@idGuiaRemision

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleGuiaRemision] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleNotaIngreso] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleNotaIngreso]
    @cantidad decimal(8,4),
	@idBien int,
	@idNotaIngreso int,
	@precio money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleNotaIngreso] ([Cantidad], [IdBien], [IdNotaIngreso], [Precio])
	VALUES (@cantidad, @idBien, @idNotaIngreso, @precio)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleNotaIngreso]
    @cantidad decimal(8,4),
	@idBien int,
	@idNotaIngreso int,
	@precio money
AS
BEGIN

	--The [dbo].[DetalleNotaIngreso] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleNotaIngreso] 
	SET [Cantidad] = @cantidad, [Precio] = @precio
	WHERE [IdBien]=@idBien AND [IdNotaIngreso]=@idNotaIngreso

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleNotaIngreso]
	 @idBien int,
	@idNotaIngreso int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleNotaIngreso]
	WHERE [IdBien]=@idBien AND [IdNotaIngreso]=@idNotaIngreso
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleNotaIngreso]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleNotaIngreso].[Cantidad] AS 'Cantidad',
	[detalleNotaIngreso].[IdBien] AS 'IdBien',
	[detalleNotaIngreso].[IdNotaIngreso] AS 'IdNotaIngreso',
	[detalleNotaIngreso].[Precio] AS 'Precio'
FROM [dbo].[DetalleNotaIngreso] [detalleNotaIngreso]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleNotaIngresoByIdBienAndByIdNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleNotaIngresoByIdBienAndByIdNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleNotaIngresoByIdBienAndByIdNotaIngreso] 
	@idBien int,
	@idNotaIngreso int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleNotaIngreso].[Cantidad] AS 'Cantidad',
	[detalleNotaIngreso].[IdBien] AS 'IdBien',
	[detalleNotaIngreso].[IdNotaIngreso] AS 'IdNotaIngreso',
	[detalleNotaIngreso].[Precio] AS 'Precio'
	FROM [dbo].[DetalleNotaIngreso] [detalleNotaIngreso]
	WHERE [IdBien]=@idBien AND [IdNotaIngreso]=@idNotaIngreso

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleNotaIngreso] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleNotaSalida] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleNotaSalida]
    @cantidad decimal(8,4),
	@idBien int,
	@idNotaSalida int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleNotaSalida] ([Cantidad], [IdBien], [IdNotaSalida])
	VALUES (@cantidad, @idBien, @idNotaSalida)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleNotaSalida]
    @cantidad decimal(8,4),
	@idBien int,
	@idNotaSalida int
AS
BEGIN

	--The [dbo].[DetalleNotaSalida] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleNotaSalida] 
	SET [Cantidad] = @cantidad
	WHERE [IdBien]=@idBien AND [IdNotaSalida]=@idNotaSalida

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleNotaSalida]
	 @idBien int,
	@idNotaSalida int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleNotaSalida]
	WHERE [IdBien]=@idBien AND [IdNotaSalida]=@idNotaSalida
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleNotaSalida]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleNotaSalida].[Cantidad] AS 'Cantidad',
	[detalleNotaSalida].[IdBien] AS 'IdBien',
	[detalleNotaSalida].[IdNotaSalida] AS 'IdNotaSalida'
FROM [dbo].[DetalleNotaSalida] [detalleNotaSalida]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleNotaSalidaByIdBienAndByIdNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleNotaSalidaByIdBienAndByIdNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleNotaSalidaByIdBienAndByIdNotaSalida] 
	@idBien int,
	@idNotaSalida int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleNotaSalida].[Cantidad] AS 'Cantidad',
	[detalleNotaSalida].[IdBien] AS 'IdBien',
	[detalleNotaSalida].[IdNotaSalida] AS 'IdNotaSalida'
	FROM [dbo].[DetalleNotaSalida] [detalleNotaSalida]
	WHERE [IdBien]=@idBien AND [IdNotaSalida]=@idNotaSalida

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleNotaSalida] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleOrdenCompra] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleOrdenCompra]
    @cantidad decimal(8,4),
	@idBien int,
	@idOrdenCompra int,
	@monto money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleOrdenCompra] ([Cantidad], [IdBien], [IdOrdenCompra], [Monto])
	VALUES (@cantidad, @idBien, @idOrdenCompra, @monto)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleOrdenCompra]
    @cantidad decimal(8,4),
	@idBien int,
	@idOrdenCompra int,
	@monto money
AS
BEGIN

	--The [dbo].[DetalleOrdenCompra] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleOrdenCompra] 
	SET [Cantidad] = @cantidad, [Monto] = @monto
	WHERE [IdBien]=@idBien AND [IdOrdenCompra]=@idOrdenCompra

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleOrdenCompra]
	 @idBien int,
	@idOrdenCompra int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleOrdenCompra]
	WHERE [IdBien]=@idBien AND [IdOrdenCompra]=@idOrdenCompra
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleOrdenCompra]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleOrdenCompra].[Cantidad] AS 'Cantidad',
	[detalleOrdenCompra].[IdBien] AS 'IdBien',
	[detalleOrdenCompra].[IdOrdenCompra] AS 'IdOrdenCompra',
	[detalleOrdenCompra].[Monto] AS 'Monto'
FROM [dbo].[DetalleOrdenCompra] [detalleOrdenCompra]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleOrdenCompraByIdBienAndByIdOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleOrdenCompraByIdBienAndByIdOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleOrdenCompraByIdBienAndByIdOrdenCompra] 
	@idBien int,
	@idOrdenCompra int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleOrdenCompra].[Cantidad] AS 'Cantidad',
	[detalleOrdenCompra].[IdBien] AS 'IdBien',
	[detalleOrdenCompra].[IdOrdenCompra] AS 'IdOrdenCompra',
	[detalleOrdenCompra].[Monto] AS 'Monto'
	FROM [dbo].[DetalleOrdenCompra] [detalleOrdenCompra]
	WHERE [IdBien]=@idBien AND [IdOrdenCompra]=@idOrdenCompra

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleOrdenCompra] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleOrdenPedido] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleOrdenPedido]
    @cantidad decimal(8,4),
	@descripcion nvarchar(50) = NULL,
	@idBien int,
	@idOrdenPedido int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleOrdenPedido] ([Cantidad], [Descripcion], [IdBien], [IdOrdenPedido])
	VALUES (@cantidad, @descripcion, @idBien, @idOrdenPedido)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleOrdenPedido]
    @cantidad decimal(8,4),
	@descripcion nvarchar(50) = NULL,
	@idBien int,
	@idOrdenPedido int
AS
BEGIN

	--The [dbo].[DetalleOrdenPedido] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleOrdenPedido] 
	SET [Cantidad] = @cantidad, [Descripcion] = @descripcion
	WHERE [IdBien]=@idBien AND [IdOrdenPedido]=@idOrdenPedido

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleOrdenPedido]
	 @idBien int,
	@idOrdenPedido int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleOrdenPedido]
	WHERE [IdBien]=@idBien AND [IdOrdenPedido]=@idOrdenPedido
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleOrdenPedido]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleOrdenPedido].[Cantidad] AS 'Cantidad',
	[detalleOrdenPedido].[Descripcion] AS 'Descripcion',
	[detalleOrdenPedido].[IdBien] AS 'IdBien',
	[detalleOrdenPedido].[IdOrdenPedido] AS 'IdOrdenPedido'
FROM [dbo].[DetalleOrdenPedido] [detalleOrdenPedido]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleOrdenPedidoByIdBienAndByIdOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleOrdenPedidoByIdBienAndByIdOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleOrdenPedidoByIdBienAndByIdOrdenPedido] 
	@idBien int,
	@idOrdenPedido int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleOrdenPedido].[Cantidad] AS 'Cantidad',
	[detalleOrdenPedido].[Descripcion] AS 'Descripcion',
	[detalleOrdenPedido].[IdBien] AS 'IdBien',
	[detalleOrdenPedido].[IdOrdenPedido] AS 'IdOrdenPedido'
	FROM [dbo].[DetalleOrdenPedido] [detalleOrdenPedido]
	WHERE [IdBien]=@idBien AND [IdOrdenPedido]=@idOrdenPedido

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleOrdenPedido] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetallePedido] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetallePedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetallePedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetallePedido]
    @cantidad decimal(8,4),
	@descripcion nvarchar(100),
	@estado bit,
	@idBien int,
	@idPedido int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetallePedido] ([Cantidad], [Descripcion], [Estado], [IdBien], [IdPedido])
	VALUES (@cantidad, @descripcion, @estado, @idBien, @idPedido)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetallePedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetallePedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetallePedido]
    @cantidad decimal(8,4),
	@descripcion nvarchar(100),
	@estado bit,
	@idBien int,
	@idPedido int
AS
BEGIN

	--The [dbo].[DetallePedido] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetallePedido] 
	SET [Cantidad] = @cantidad, [Descripcion] = @descripcion, [Estado] = @estado
	WHERE [IdBien]=@idBien AND [IdPedido]=@idPedido

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetallePedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetallePedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetallePedido]
	 @idBien int,
	@idPedido int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetallePedido]
	WHERE [IdBien]=@idBien AND [IdPedido]=@idPedido
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetallePedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetallePedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetallePedido]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detallePedido].[Cantidad] AS 'Cantidad',
	[detallePedido].[Descripcion] AS 'Descripcion',
	[detallePedido].[Estado] AS 'Estado',
	[detallePedido].[IdBien] AS 'IdBien',
	[detallePedido].[IdPedido] AS 'IdPedido'
FROM [dbo].[DetallePedido] [detallePedido]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetallePedidoByIdBienAndByIdPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetallePedidoByIdBienAndByIdPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetallePedidoByIdBienAndByIdPedido] 
	@idBien int,
	@idPedido int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detallePedido].[Cantidad] AS 'Cantidad',
	[detallePedido].[Descripcion] AS 'Descripcion',
	[detallePedido].[Estado] AS 'Estado',
	[detallePedido].[IdBien] AS 'IdBien',
	[detallePedido].[IdPedido] AS 'IdPedido'
	FROM [dbo].[DetallePedido] [detallePedido]
	WHERE [IdBien]=@idBien AND [IdPedido]=@idPedido

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetallePedido] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetallePlanCompra] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetallePlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetallePlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetallePlanCompra]
    @cantidad decimal(8,4),
	@descripcion nvarchar(50) = NULL,
	@estado bit,
	@idBien int,
	@idPlanCompra int,
	@monto money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetallePlanCompra] ([Cantidad], [Descripcion], [Estado], [IdBien], [IdPlanCompra], [Monto])
	VALUES (@cantidad, @descripcion, @estado, @idBien, @idPlanCompra, @monto)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetallePlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetallePlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetallePlanCompra]
    @cantidad decimal(8,4),
	@descripcion nvarchar(50) = NULL,
	@estado bit,
	@idBien int,
	@idPlanCompra int,
	@monto money
AS
BEGIN

	--The [dbo].[DetallePlanCompra] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetallePlanCompra] 
	SET [Cantidad] = @cantidad, [Descripcion] = @descripcion, [Estado] = @estado, [Monto] = @monto
	WHERE [IdBien]=@idBien AND [IdPlanCompra]=@idPlanCompra

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetallePlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetallePlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetallePlanCompra]
	 @idBien int,
	@idPlanCompra int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetallePlanCompra]
	WHERE [IdBien]=@idBien AND [IdPlanCompra]=@idPlanCompra
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetallePlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetallePlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetallePlanCompra]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detallePlanCompra].[Cantidad] AS 'Cantidad',
	[detallePlanCompra].[Descripcion] AS 'Descripcion',
	[detallePlanCompra].[Estado] AS 'Estado',
	[detallePlanCompra].[IdBien] AS 'IdBien',
	[detallePlanCompra].[IdPlanCompra] AS 'IdPlanCompra',
	[detallePlanCompra].[Monto] AS 'Monto'
FROM [dbo].[DetallePlanCompra] [detallePlanCompra]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetallePlanCompraByIdBienAndByIdPlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetallePlanCompraByIdBienAndByIdPlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetallePlanCompraByIdBienAndByIdPlanCompra] 
	@idBien int,
	@idPlanCompra int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detallePlanCompra].[Cantidad] AS 'Cantidad',
	[detallePlanCompra].[Descripcion] AS 'Descripcion',
	[detallePlanCompra].[Estado] AS 'Estado',
	[detallePlanCompra].[IdBien] AS 'IdBien',
	[detallePlanCompra].[IdPlanCompra] AS 'IdPlanCompra',
	[detallePlanCompra].[Monto] AS 'Monto'
	FROM [dbo].[DetallePlanCompra] [detallePlanCompra]
	WHERE [IdBien]=@idBien AND [IdPlanCompra]=@idPlanCompra

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetallePlanCompra] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleSubasta] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleSubasta]
    @cantidad decimal(8,4),
	@estado nvarchar(50) = NULL,
	@idBien int,
	@idSubasta int,
	@montoInicial money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleSubasta] ([Cantidad], [Estado], [IdBien], [IdSubasta], [MontoInicial])
	VALUES (@cantidad, @estado, @idBien, @idSubasta, @montoInicial)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleSubasta]
    @cantidad decimal(8,4),
	@estado nvarchar(50) = NULL,
	@idBien int,
	@idSubasta int,
	@montoInicial money
AS
BEGIN

	--The [dbo].[DetalleSubasta] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleSubasta] 
	SET [Cantidad] = @cantidad, [Estado] = @estado, [MontoInicial] = @montoInicial
	WHERE [IdBien]=@idBien AND [IdSubasta]=@idSubasta

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleSubasta]
	 @idBien int,
	@idSubasta int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleSubasta]
	WHERE [IdBien]=@idBien AND [IdSubasta]=@idSubasta
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleSubasta]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleSubasta].[Cantidad] AS 'Cantidad',
	[detalleSubasta].[Estado] AS 'Estado',
	[detalleSubasta].[IdBien] AS 'IdBien',
	[detalleSubasta].[IdSubasta] AS 'IdSubasta',
	[detalleSubasta].[MontoInicial] AS 'MontoInicial'
FROM [dbo].[DetalleSubasta] [detalleSubasta]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleSubastaByIdBienAndByIdSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleSubastaByIdBienAndByIdSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleSubastaByIdBienAndByIdSubasta] 
	@idBien int,
	@idSubasta int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleSubasta].[Cantidad] AS 'Cantidad',
	[detalleSubasta].[Estado] AS 'Estado',
	[detalleSubasta].[IdBien] AS 'IdBien',
	[detalleSubasta].[IdSubasta] AS 'IdSubasta',
	[detalleSubasta].[MontoInicial] AS 'MontoInicial'
	FROM [dbo].[DetalleSubasta] [detalleSubasta]
	WHERE [IdBien]=@idBien AND [IdSubasta]=@idSubasta

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleSubasta] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[DetalleVenta] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertDetalleVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertDetalleVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertDetalleVenta]
    @cantida decimal(8,4),
	@descripcion nvarchar(50) = NULL,
	@idBien int,
	@idVenta int,
	@monto money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[DetalleVenta] ([Cantida], [Descripcion], [IdBien], [IdVenta], [Monto])
	VALUES (@cantida, @descripcion, @idBien, @idVenta, @monto)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateDetalleVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateDetalleVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateDetalleVenta]
    @cantida decimal(8,4),
	@descripcion nvarchar(50) = NULL,
	@idBien int,
	@idVenta int,
	@monto money
AS
BEGIN

	--The [dbo].[DetalleVenta] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[DetalleVenta] 
	SET [Cantida] = @cantida, [Descripcion] = @descripcion, [Monto] = @monto
	WHERE [IdBien]=@idBien AND [IdVenta]=@idVenta

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteDetalleVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteDetalleVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteDetalleVenta]
	 @idBien int,
	@idVenta int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[DetalleVenta]
	WHERE [IdBien]=@idBien AND [IdVenta]=@idVenta
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromDetalleVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromDetalleVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromDetalleVenta]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[detalleVenta].[Cantida] AS 'Cantida',
	[detalleVenta].[Descripcion] AS 'Descripcion',
	[detalleVenta].[IdBien] AS 'IdBien',
	[detalleVenta].[IdVenta] AS 'IdVenta',
	[detalleVenta].[Monto] AS 'Monto'
FROM [dbo].[DetalleVenta] [detalleVenta]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetDetalleVentaByIdBienAndByIdVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetDetalleVentaByIdBienAndByIdVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetDetalleVentaByIdBienAndByIdVenta] 
	@idBien int,
	@idVenta int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[detalleVenta].[Cantida] AS 'Cantida',
	[detalleVenta].[Descripcion] AS 'Descripcion',
	[detalleVenta].[IdBien] AS 'IdBien',
	[detalleVenta].[IdVenta] AS 'IdVenta',
	[detalleVenta].[Monto] AS 'Monto'
	FROM [dbo].[DetalleVenta] [detalleVenta]
	WHERE [IdBien]=@idBien AND [IdVenta]=@idVenta

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[DetalleVenta] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Empleados] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertEmpleados')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertEmpleados] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertEmpleados]
    @apellidos nvarchar(80),
	@direccion nvarchar(50),
	@dNI varchar(8) = NULL,
	@email nvarchar(50) = NULL,
	@idCargo int,
	@idEmpleado smallint OUT,
	@nombre nvarchar(50),
	@telefono varchar(15) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Empleados] ([Apellidos], [Direccion], [DNI], [Email], [IdCargo], [Nombre], [Telefono])
	VALUES (@apellidos, @direccion, @dNI, @email, @idCargo, @nombre, @telefono)
    SET @idEmpleado = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateEmpleados')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateEmpleados] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateEmpleados]
    @apellidos nvarchar(80),
	@direccion nvarchar(50),
	@dNI varchar(8) = NULL,
	@email nvarchar(50) = NULL,
	@idCargo int,
	@idEmpleado smallint,
	@nombre nvarchar(50),
	@telefono varchar(15) = NULL
AS
BEGIN

	--The [dbo].[Empleados] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Empleados] 
	SET [Apellidos] = @apellidos, [Direccion] = @direccion, [DNI] = @dNI, [Email] = @email, [IdCargo] = @idCargo, [Nombre] = @nombre, [Telefono] = @telefono
	WHERE [IdEmpleado]=@idEmpleado

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteEmpleados')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteEmpleados] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteEmpleados]
	 @idEmpleado smallint
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Empleados]
	WHERE [IdEmpleado]=@idEmpleado
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromEmpleados')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromEmpleados] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromEmpleados]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[empleados].[Apellidos] AS 'Apellidos',
	[empleados].[Direccion] AS 'Direccion',
	[empleados].[DNI] AS 'DNI',
	[empleados].[Email] AS 'Email',
	[empleados].[IdCargo] AS 'IdCargo',
	[empleados].[IdEmpleado] AS 'IdEmpleado',
	[empleados].[Nombre] AS 'Nombre',
	[empleados].[Telefono] AS 'Telefono'
FROM [dbo].[Empleados] [empleados]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetEmpleadosByIdEmpleado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetEmpleadosByIdEmpleado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetEmpleadosByIdEmpleado] 
	@idEmpleado smallint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[empleados].[Apellidos] AS 'Apellidos',
	[empleados].[Direccion] AS 'Direccion',
	[empleados].[DNI] AS 'DNI',
	[empleados].[Email] AS 'Email',
	[empleados].[IdCargo] AS 'IdCargo',
	[empleados].[IdEmpleado] AS 'IdEmpleado',
	[empleados].[Nombre] AS 'Nombre',
	[empleados].[Telefono] AS 'Telefono'
	FROM [dbo].[Empleados] [empleados]
	WHERE [IdEmpleado]=@idEmpleado

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetEmpleadosByIdCargo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetEmpleadosByIdCargo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetEmpleadosByIdCargo] 
	@idCargo int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[empleados].[Apellidos] AS 'Apellidos',
	[empleados].[Direccion] AS 'Direccion',
	[empleados].[DNI] AS 'DNI',
	[empleados].[Email] AS 'Email',
	[empleados].[IdCargo] AS 'IdCargo',
	[empleados].[IdEmpleado] AS 'IdEmpleado',
	[empleados].[Nombre] AS 'Nombre',
	[empleados].[Telefono] AS 'Telefono'
	FROM [dbo].[Empleados] [empleados]
	WHERE [IdCargo]=@idCargo

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Factura] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertFactura]
    @descripcion nvarchar(200) = NULL,
	@fecha datetime,
	@idEmpleado smallint,
	@idFactura int OUT,
	@idOrdenCompra int,
	@numero nvarchar(7),
	@serie nvarchar(4)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Factura] ([Descripcion], [Fecha], [IdEmpleado], [IdOrdenCompra], [Numero], [Serie])
	VALUES (@descripcion, @fecha, @idEmpleado, @idOrdenCompra, @numero, @serie)
    SET @idFactura = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateFactura]
    @descripcion nvarchar(200) = NULL,
	@fecha datetime,
	@idEmpleado smallint,
	@idFactura int,
	@idOrdenCompra int,
	@numero nvarchar(7),
	@serie nvarchar(4)
AS
BEGIN

	--The [dbo].[Factura] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Factura] 
	SET [Descripcion] = @descripcion, [Fecha] = @fecha, [IdEmpleado] = @idEmpleado, [IdOrdenCompra] = @idOrdenCompra, [Numero] = @numero, [Serie] = @serie
	WHERE [IdFactura]=@idFactura

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteFactura]
	 @idFactura int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Factura]
	WHERE [IdFactura]=@idFactura
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromFactura]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[factura].[Descripcion] AS 'Descripcion',
	[factura].[Fecha] AS 'Fecha',
	[factura].[IdEmpleado] AS 'IdEmpleado',
	[factura].[IdFactura] AS 'IdFactura',
	[factura].[IdOrdenCompra] AS 'IdOrdenCompra',
	[factura].[Numero] AS 'Numero',
	[factura].[Serie] AS 'Serie'
FROM [dbo].[Factura] [factura]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetFacturaByIdFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetFacturaByIdFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetFacturaByIdFactura] 
	@idFactura int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[factura].[Descripcion] AS 'Descripcion',
	[factura].[Fecha] AS 'Fecha',
	[factura].[IdEmpleado] AS 'IdEmpleado',
	[factura].[IdFactura] AS 'IdFactura',
	[factura].[IdOrdenCompra] AS 'IdOrdenCompra',
	[factura].[Numero] AS 'Numero',
	[factura].[Serie] AS 'Serie'
	FROM [dbo].[Factura] [factura]
	WHERE [IdFactura]=@idFactura

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetFacturaByIdEmpleadoAndByIdOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetFacturaByIdEmpleadoAndByIdOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetFacturaByIdEmpleadoAndByIdOrdenCompra] 
	@idEmpleado smallint,
	@idOrdenCompra int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[factura].[Descripcion] AS 'Descripcion',
	[factura].[Fecha] AS 'Fecha',
	[factura].[IdEmpleado] AS 'IdEmpleado',
	[factura].[IdFactura] AS 'IdFactura',
	[factura].[IdOrdenCompra] AS 'IdOrdenCompra',
	[factura].[Numero] AS 'Numero',
	[factura].[Serie] AS 'Serie'
	FROM [dbo].[Factura] [factura]
	WHERE [IdEmpleado]=@idEmpleado AND [IdOrdenCompra]=@idOrdenCompra

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Formulario] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertFormulario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertFormulario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertFormulario]
    @className varchar(30),
	@descripcion nvarchar(150) = NULL,
	@estado bit,
	@idFormulario tinyint OUT,
	@idMenu tinyint,
	@nombre varchar(80)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Formulario] ([ClassName], [Descripcion], [Estado], [IdMenu], [Nombre])
	VALUES (@className, @descripcion, @estado, @idMenu, @nombre)
    SET @idFormulario = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateFormulario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateFormulario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateFormulario]
    @className varchar(30),
	@descripcion nvarchar(150) = NULL,
	@estado bit,
	@idFormulario tinyint,
	@idMenu tinyint,
	@nombre varchar(80)
AS
BEGIN

	--The [dbo].[Formulario] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Formulario] 
	SET [ClassName] = @className, [Descripcion] = @descripcion, [Estado] = @estado, [IdMenu] = @idMenu, [Nombre] = @nombre
	WHERE [IdFormulario]=@idFormulario

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteFormulario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteFormulario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteFormulario]
	 @idFormulario tinyint
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Formulario]
	WHERE [IdFormulario]=@idFormulario
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromFormulario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromFormulario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromFormulario]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[formulario].[ClassName] AS 'ClassName',
	[formulario].[Descripcion] AS 'Descripcion',
	[formulario].[Estado] AS 'Estado',
	[formulario].[IdFormulario] AS 'IdFormulario',
	[formulario].[IdMenu] AS 'IdMenu',
	[formulario].[Nombre] AS 'Nombre'
FROM [dbo].[Formulario] [formulario]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetFormularioByIdFormulario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetFormularioByIdFormulario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetFormularioByIdFormulario] 
	@idFormulario tinyint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[formulario].[ClassName] AS 'ClassName',
	[formulario].[Descripcion] AS 'Descripcion',
	[formulario].[Estado] AS 'Estado',
	[formulario].[IdFormulario] AS 'IdFormulario',
	[formulario].[IdMenu] AS 'IdMenu',
	[formulario].[Nombre] AS 'Nombre'
	FROM [dbo].[Formulario] [formulario]
	WHERE [IdFormulario]=@idFormulario

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetFormularioByIdMenu')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetFormularioByIdMenu] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetFormularioByIdMenu] 
	@idMenu tinyint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[formulario].[ClassName] AS 'ClassName',
	[formulario].[Descripcion] AS 'Descripcion',
	[formulario].[Estado] AS 'Estado',
	[formulario].[IdFormulario] AS 'IdFormulario',
	[formulario].[IdMenu] AS 'IdMenu',
	[formulario].[Nombre] AS 'Nombre'
	FROM [dbo].[Formulario] [formulario]
	WHERE [IdMenu]=@idMenu

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[GuiaRemision] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertGuiaRemision]
    @descripcion nvarchar(250) = NULL,
	@destino nvarchar(50),
	@fechaEmision datetime,
	@fechaTranslado datetime,
	@idDocumento int,
	@idEmpleado smallint,
	@idGuiaRemision int OUT,
	@motivo varchar(50) = NULL,
	@origen nvarchar(50),
	@tipoGuia int,
	@transporte nvarchar(50) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[GuiaRemision] ([Descripcion], [Destino], [FechaEmision], [FechaTranslado], [IdDocumento], [IdEmpleado], [Motivo], [Origen], [TipoGuia], [Transporte])
	VALUES (@descripcion, @destino, @fechaEmision, @fechaTranslado, @idDocumento, @idEmpleado, @motivo, @origen, @tipoGuia, @transporte)
    SET @idGuiaRemision = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateGuiaRemision]
    @descripcion nvarchar(250) = NULL,
	@destino nvarchar(50),
	@fechaEmision datetime,
	@fechaTranslado datetime,
	@idDocumento int,
	@idEmpleado smallint,
	@idGuiaRemision int,
	@motivo varchar(50) = NULL,
	@origen nvarchar(50),
	@tipoGuia int,
	@transporte nvarchar(50) = NULL
AS
BEGIN

	--The [dbo].[GuiaRemision] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[GuiaRemision] 
	SET [Descripcion] = @descripcion, [Destino] = @destino, [FechaEmision] = @fechaEmision, [FechaTranslado] = @fechaTranslado, [IdDocumento] = @idDocumento, [IdEmpleado] = @idEmpleado, [Motivo] = @motivo, [Origen] = @origen, [TipoGuia] = @tipoGuia, [Transporte] = @transporte
	WHERE [IdGuiaRemision]=@idGuiaRemision

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteGuiaRemision]
	 @idGuiaRemision int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[GuiaRemision]
	WHERE [IdGuiaRemision]=@idGuiaRemision
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromGuiaRemision]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[guiaRemision].[Descripcion] AS 'Descripcion',
	[guiaRemision].[Destino] AS 'Destino',
	[guiaRemision].[FechaEmision] AS 'FechaEmision',
	[guiaRemision].[FechaTranslado] AS 'FechaTranslado',
	[guiaRemision].[IdDocumento] AS 'IdDocumento',
	[guiaRemision].[IdEmpleado] AS 'IdEmpleado',
	[guiaRemision].[IdGuiaRemision] AS 'IdGuiaRemision',
	[guiaRemision].[Motivo] AS 'Motivo',
	[guiaRemision].[Origen] AS 'Origen',
	[guiaRemision].[TipoGuia] AS 'TipoGuia',
	[guiaRemision].[Transporte] AS 'Transporte'
FROM [dbo].[GuiaRemision] [guiaRemision]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetGuiaRemisionByIdGuiaRemision')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetGuiaRemisionByIdGuiaRemision] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetGuiaRemisionByIdGuiaRemision] 
	@idGuiaRemision int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[guiaRemision].[Descripcion] AS 'Descripcion',
	[guiaRemision].[Destino] AS 'Destino',
	[guiaRemision].[FechaEmision] AS 'FechaEmision',
	[guiaRemision].[FechaTranslado] AS 'FechaTranslado',
	[guiaRemision].[IdDocumento] AS 'IdDocumento',
	[guiaRemision].[IdEmpleado] AS 'IdEmpleado',
	[guiaRemision].[IdGuiaRemision] AS 'IdGuiaRemision',
	[guiaRemision].[Motivo] AS 'Motivo',
	[guiaRemision].[Origen] AS 'Origen',
	[guiaRemision].[TipoGuia] AS 'TipoGuia',
	[guiaRemision].[Transporte] AS 'Transporte'
	FROM [dbo].[GuiaRemision] [guiaRemision]
	WHERE [IdGuiaRemision]=@idGuiaRemision

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetGuiaRemisionByIdEmpleado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetGuiaRemisionByIdEmpleado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetGuiaRemisionByIdEmpleado] 
	@idEmpleado smallint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[guiaRemision].[Descripcion] AS 'Descripcion',
	[guiaRemision].[Destino] AS 'Destino',
	[guiaRemision].[FechaEmision] AS 'FechaEmision',
	[guiaRemision].[FechaTranslado] AS 'FechaTranslado',
	[guiaRemision].[IdDocumento] AS 'IdDocumento',
	[guiaRemision].[IdEmpleado] AS 'IdEmpleado',
	[guiaRemision].[IdGuiaRemision] AS 'IdGuiaRemision',
	[guiaRemision].[Motivo] AS 'Motivo',
	[guiaRemision].[Origen] AS 'Origen',
	[guiaRemision].[TipoGuia] AS 'TipoGuia',
	[guiaRemision].[Transporte] AS 'Transporte'
	FROM [dbo].[GuiaRemision] [guiaRemision]
	WHERE [IdEmpleado]=@idEmpleado

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Juridico] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertJuridico')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertJuridico] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertJuridico]
    @giroNegocio nvarchar(50) = NULL,
	@idCliente int,
	@idJuridico int OUT,
	@razonSocial nvarchar(50),
	@rUC char(11)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Juridico] ([GiroNegocio], [IdCliente], [RazonSocial], [RUC])
	VALUES (@giroNegocio, @idCliente, @razonSocial, @rUC)
    SET @idJuridico = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateJuridico')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateJuridico] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateJuridico]
    @giroNegocio nvarchar(50) = NULL,
	@idCliente int,
	@idJuridico int,
	@razonSocial nvarchar(50),
	@rUC char(11)
AS
BEGIN

	--The [dbo].[Juridico] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Juridico] 
	SET [GiroNegocio] = @giroNegocio, [RazonSocial] = @razonSocial, [RUC] = @rUC
	WHERE [IdCliente]=@idCliente AND [IdJuridico]=@idJuridico

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteJuridico')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteJuridico] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteJuridico]
	 @idCliente int,
	@idJuridico int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Juridico]
	WHERE [IdCliente]=@idCliente AND [IdJuridico]=@idJuridico
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromJuridico')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromJuridico] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromJuridico]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[juridico].[GiroNegocio] AS 'GiroNegocio',
	[juridico].[IdCliente] AS 'IdCliente',
	[juridico].[IdJuridico] AS 'IdJuridico',
	[juridico].[RazonSocial] AS 'RazonSocial',
	[juridico].[RUC] AS 'RUC'
FROM [dbo].[Juridico] [juridico]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetJuridicoByIdClienteAndByIdJuridico')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetJuridicoByIdClienteAndByIdJuridico] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetJuridicoByIdClienteAndByIdJuridico] 
	@idCliente int,
	@idJuridico int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[juridico].[GiroNegocio] AS 'GiroNegocio',
	[juridico].[IdCliente] AS 'IdCliente',
	[juridico].[IdJuridico] AS 'IdJuridico',
	[juridico].[RazonSocial] AS 'RazonSocial',
	[juridico].[RUC] AS 'RUC'
	FROM [dbo].[Juridico] [juridico]
	WHERE [IdCliente]=@idCliente AND [IdJuridico]=@idJuridico

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Juridico] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Mantenimiento] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertMantenimiento')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertMantenimiento] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertMantenimiento]
    @descripcion nvarchar(200) = NULL,
	@estado int,
	@fechaEntrega datetime,
	@fechaIngreso datetime,
	@idBien int,
	@idMantenimiento int OUT
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Mantenimiento] ([Descripcion], [Estado], [FechaEntrega], [FechaIngreso], [IdBien])
	VALUES (@descripcion, @estado, @fechaEntrega, @fechaIngreso, @idBien)
    SET @idMantenimiento = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateMantenimiento')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateMantenimiento] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateMantenimiento]
    @descripcion nvarchar(200) = NULL,
	@estado int,
	@fechaEntrega datetime,
	@fechaIngreso datetime,
	@idBien int,
	@idMantenimiento int
AS
BEGIN

	--The [dbo].[Mantenimiento] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Mantenimiento] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [FechaEntrega] = @fechaEntrega, [FechaIngreso] = @fechaIngreso
	WHERE [IdBien]=@idBien AND [IdMantenimiento]=@idMantenimiento

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteMantenimiento')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteMantenimiento] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteMantenimiento]
	 @idBien int,
	@idMantenimiento int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Mantenimiento]
	WHERE [IdBien]=@idBien AND [IdMantenimiento]=@idMantenimiento
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromMantenimiento')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromMantenimiento] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromMantenimiento]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[mantenimiento].[Descripcion] AS 'Descripcion',
	[mantenimiento].[Estado] AS 'Estado',
	[mantenimiento].[FechaEntrega] AS 'FechaEntrega',
	[mantenimiento].[FechaIngreso] AS 'FechaIngreso',
	[mantenimiento].[IdBien] AS 'IdBien',
	[mantenimiento].[IdMantenimiento] AS 'IdMantenimiento'
FROM [dbo].[Mantenimiento] [mantenimiento]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetMantenimientoByIdBienAndByIdMantenimiento')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetMantenimientoByIdBienAndByIdMantenimiento] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetMantenimientoByIdBienAndByIdMantenimiento] 
	@idBien int,
	@idMantenimiento int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[mantenimiento].[Descripcion] AS 'Descripcion',
	[mantenimiento].[Estado] AS 'Estado',
	[mantenimiento].[FechaEntrega] AS 'FechaEntrega',
	[mantenimiento].[FechaIngreso] AS 'FechaIngreso',
	[mantenimiento].[IdBien] AS 'IdBien',
	[mantenimiento].[IdMantenimiento] AS 'IdMantenimiento'
	FROM [dbo].[Mantenimiento] [mantenimiento]
	WHERE [IdBien]=@idBien AND [IdMantenimiento]=@idMantenimiento

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Mantenimiento] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Menus] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertMenus')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertMenus] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertMenus]
    @descripcion nvarchar(150) = NULL,
	@estado bit,
	@idMenu tinyint OUT,
	@idMenudPadre tinyint,
	@namespace varchar(200),
	@nombre varchar(40)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Menus] ([Descripcion], [Estado], [IdMenudPadre], [Namespace], [Nombre])
	VALUES (@descripcion, @estado, @idMenudPadre, @namespace, @nombre)
    SET @idMenu = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateMenus')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateMenus] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateMenus]
    @descripcion nvarchar(150) = NULL,
	@estado bit,
	@idMenu tinyint,
	@idMenudPadre tinyint,
	@namespace varchar(200),
	@nombre varchar(40)
AS
BEGIN

	--The [dbo].[Menus] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Menus] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [IdMenudPadre] = @idMenudPadre, [Namespace] = @namespace, [Nombre] = @nombre
	WHERE [IdMenu]=@idMenu

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteMenus')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteMenus] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteMenus]
	 @idMenu tinyint
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Menus]
	WHERE [IdMenu]=@idMenu
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromMenus')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromMenus] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromMenus]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[menus].[Descripcion] AS 'Descripcion',
	[menus].[Estado] AS 'Estado',
	[menus].[IdMenu] AS 'IdMenu',
	[menus].[IdMenudPadre] AS 'IdMenudPadre',
	[menus].[Namespace] AS 'Namespace',
	[menus].[Nombre] AS 'Nombre'
FROM [dbo].[Menus] [menus]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetMenusByIdMenu')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetMenusByIdMenu] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetMenusByIdMenu] 
	@idMenu tinyint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[menus].[Descripcion] AS 'Descripcion',
	[menus].[Estado] AS 'Estado',
	[menus].[IdMenu] AS 'IdMenu',
	[menus].[IdMenudPadre] AS 'IdMenudPadre',
	[menus].[Namespace] AS 'Namespace',
	[menus].[Nombre] AS 'Nombre'
	FROM [dbo].[Menus] [menus]
	WHERE [IdMenu]=@idMenu

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetMenusByIdMenudPadre')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetMenusByIdMenudPadre] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetMenusByIdMenudPadre] 
	@idMenudPadre tinyint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[menus].[Descripcion] AS 'Descripcion',
	[menus].[Estado] AS 'Estado',
	[menus].[IdMenu] AS 'IdMenu',
	[menus].[IdMenudPadre] AS 'IdMenudPadre',
	[menus].[Namespace] AS 'Namespace',
	[menus].[Nombre] AS 'Nombre'
	FROM [dbo].[Menus] [menus]
	WHERE [IdMenudPadre]=@idMenudPadre

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Natural] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertNatural')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertNatural] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertNatural]
    @apellidos nvarchar(50),
	@dNI char(8),
	@idCliente int,
	@idNatural int OUT,
	@nombre nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Natural] ([Apellidos], [DNI], [IdCliente], [Nombre])
	VALUES (@apellidos, @dNI, @idCliente, @nombre)
    SET @idNatural = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateNatural')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateNatural] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateNatural]
    @apellidos nvarchar(50),
	@dNI char(8),
	@idCliente int,
	@idNatural int,
	@nombre nvarchar(50)
AS
BEGIN

	--The [dbo].[Natural] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Natural] 
	SET [Apellidos] = @apellidos, [DNI] = @dNI, [Nombre] = @nombre
	WHERE [IdCliente]=@idCliente AND [IdNatural]=@idNatural

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteNatural')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteNatural] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteNatural]
	 @idCliente int,
	@idNatural int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Natural]
	WHERE [IdCliente]=@idCliente AND [IdNatural]=@idNatural
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromNatural')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromNatural] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromNatural]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[natural].[Apellidos] AS 'Apellidos',
	[natural].[DNI] AS 'DNI',
	[natural].[IdCliente] AS 'IdCliente',
	[natural].[IdNatural] AS 'IdNatural',
	[natural].[Nombre] AS 'Nombre'
FROM [dbo].[Natural] [natural]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetNaturalByIdClienteAndByIdNatural')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetNaturalByIdClienteAndByIdNatural] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetNaturalByIdClienteAndByIdNatural] 
	@idCliente int,
	@idNatural int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[natural].[Apellidos] AS 'Apellidos',
	[natural].[DNI] AS 'DNI',
	[natural].[IdCliente] AS 'IdCliente',
	[natural].[IdNatural] AS 'IdNatural',
	[natural].[Nombre] AS 'Nombre'
	FROM [dbo].[Natural] [natural]
	WHERE [IdCliente]=@idCliente AND [IdNatural]=@idNatural

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Natural] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[NotaIngreso] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertNotaIngreso]
    @descripcion nvarchar(200) = NULL,
	@estado int,
	@fechaIngreso datetime,
	@idEmpleado smallint,
	@idFactura int,
	@idNotaIngreso int OUT
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[NotaIngreso] ([Descripcion], [Estado], [FechaIngreso], [IdEmpleado], [IdFactura])
	VALUES (@descripcion, @estado, @fechaIngreso, @idEmpleado, @idFactura)
    SET @idNotaIngreso = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateNotaIngreso]
    @descripcion nvarchar(200) = NULL,
	@estado int,
	@fechaIngreso datetime,
	@idEmpleado smallint,
	@idFactura int,
	@idNotaIngreso int
AS
BEGIN

	--The [dbo].[NotaIngreso] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[NotaIngreso] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [FechaIngreso] = @fechaIngreso, [IdEmpleado] = @idEmpleado, [IdFactura] = @idFactura
	WHERE [IdNotaIngreso]=@idNotaIngreso

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteNotaIngreso]
	 @idNotaIngreso int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[NotaIngreso]
	WHERE [IdNotaIngreso]=@idNotaIngreso
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromNotaIngreso]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[notaIngreso].[Descripcion] AS 'Descripcion',
	[notaIngreso].[Estado] AS 'Estado',
	[notaIngreso].[FechaIngreso] AS 'FechaIngreso',
	[notaIngreso].[IdEmpleado] AS 'IdEmpleado',
	[notaIngreso].[IdFactura] AS 'IdFactura',
	[notaIngreso].[IdNotaIngreso] AS 'IdNotaIngreso'
FROM [dbo].[NotaIngreso] [notaIngreso]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetNotaIngresoByIdNotaIngreso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetNotaIngresoByIdNotaIngreso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetNotaIngresoByIdNotaIngreso] 
	@idNotaIngreso int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[notaIngreso].[Descripcion] AS 'Descripcion',
	[notaIngreso].[Estado] AS 'Estado',
	[notaIngreso].[FechaIngreso] AS 'FechaIngreso',
	[notaIngreso].[IdEmpleado] AS 'IdEmpleado',
	[notaIngreso].[IdFactura] AS 'IdFactura',
	[notaIngreso].[IdNotaIngreso] AS 'IdNotaIngreso'
	FROM [dbo].[NotaIngreso] [notaIngreso]
	WHERE [IdNotaIngreso]=@idNotaIngreso

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetNotaIngresoByIdEmpleadoAndByIdFactura')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetNotaIngresoByIdEmpleadoAndByIdFactura] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetNotaIngresoByIdEmpleadoAndByIdFactura] 
	@idEmpleado smallint,
	@idFactura int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[notaIngreso].[Descripcion] AS 'Descripcion',
	[notaIngreso].[Estado] AS 'Estado',
	[notaIngreso].[FechaIngreso] AS 'FechaIngreso',
	[notaIngreso].[IdEmpleado] AS 'IdEmpleado',
	[notaIngreso].[IdFactura] AS 'IdFactura',
	[notaIngreso].[IdNotaIngreso] AS 'IdNotaIngreso'
	FROM [dbo].[NotaIngreso] [notaIngreso]
	WHERE [IdEmpleado]=@idEmpleado AND [IdFactura]=@idFactura

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[NotaSalida] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertNotaSalida]
    @descripcion nvarchar(150) = NULL,
	@fecha datetime,
	@idEmpleado smallint,
	@idNotaSalida int OUT,
	@idOrdenPedido int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[NotaSalida] ([Descripcion], [Fecha], [IdEmpleado], [IdOrdenPedido])
	VALUES (@descripcion, @fecha, @idEmpleado, @idOrdenPedido)
    SET @idNotaSalida = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateNotaSalida]
    @descripcion nvarchar(150) = NULL,
	@fecha datetime,
	@idEmpleado smallint,
	@idNotaSalida int,
	@idOrdenPedido int
AS
BEGIN

	--The [dbo].[NotaSalida] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[NotaSalida] 
	SET [Descripcion] = @descripcion, [Fecha] = @fecha, [IdEmpleado] = @idEmpleado, [IdOrdenPedido] = @idOrdenPedido
	WHERE [IdNotaSalida]=@idNotaSalida

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteNotaSalida]
	 @idNotaSalida int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[NotaSalida]
	WHERE [IdNotaSalida]=@idNotaSalida
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromNotaSalida]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[notaSalida].[Descripcion] AS 'Descripcion',
	[notaSalida].[Fecha] AS 'Fecha',
	[notaSalida].[IdEmpleado] AS 'IdEmpleado',
	[notaSalida].[IdNotaSalida] AS 'IdNotaSalida',
	[notaSalida].[IdOrdenPedido] AS 'IdOrdenPedido'
FROM [dbo].[NotaSalida] [notaSalida]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetNotaSalidaByIdNotaSalida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetNotaSalidaByIdNotaSalida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetNotaSalidaByIdNotaSalida] 
	@idNotaSalida int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[notaSalida].[Descripcion] AS 'Descripcion',
	[notaSalida].[Fecha] AS 'Fecha',
	[notaSalida].[IdEmpleado] AS 'IdEmpleado',
	[notaSalida].[IdNotaSalida] AS 'IdNotaSalida',
	[notaSalida].[IdOrdenPedido] AS 'IdOrdenPedido'
	FROM [dbo].[NotaSalida] [notaSalida]
	WHERE [IdNotaSalida]=@idNotaSalida

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetNotaSalidaByIdEmpleadoAndByIdOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetNotaSalidaByIdEmpleadoAndByIdOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetNotaSalidaByIdEmpleadoAndByIdOrdenPedido] 
	@idEmpleado smallint,
	@idOrdenPedido int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[notaSalida].[Descripcion] AS 'Descripcion',
	[notaSalida].[Fecha] AS 'Fecha',
	[notaSalida].[IdEmpleado] AS 'IdEmpleado',
	[notaSalida].[IdNotaSalida] AS 'IdNotaSalida',
	[notaSalida].[IdOrdenPedido] AS 'IdOrdenPedido'
	FROM [dbo].[NotaSalida] [notaSalida]
	WHERE [IdEmpleado]=@idEmpleado AND [IdOrdenPedido]=@idOrdenPedido

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[OrdenCompra] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertOrdenCompra]
    @descripcion nvarchar(200) = NULL,
	@estado bit,
	@fechaCompra datetime,
	@idEmpleado smallint,
	@idOrdenCompra int OUT,
	@idOrdenPedido int,
	@idProveedor int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[OrdenCompra] ([Descripcion], [Estado], [FechaCompra], [IdEmpleado], [IdOrdenPedido], [IdProveedor])
	VALUES (@descripcion, @estado, @fechaCompra, @idEmpleado, @idOrdenPedido, @idProveedor)
    SET @idOrdenCompra = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateOrdenCompra]
    @descripcion nvarchar(200) = NULL,
	@estado bit,
	@fechaCompra datetime,
	@idEmpleado smallint,
	@idOrdenCompra int,
	@idOrdenPedido int,
	@idProveedor int
AS
BEGIN

	--The [dbo].[OrdenCompra] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[OrdenCompra] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [FechaCompra] = @fechaCompra, [IdEmpleado] = @idEmpleado, [IdOrdenPedido] = @idOrdenPedido, [IdProveedor] = @idProveedor
	WHERE [IdOrdenCompra]=@idOrdenCompra

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteOrdenCompra]
	 @idOrdenCompra int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[OrdenCompra]
	WHERE [IdOrdenCompra]=@idOrdenCompra
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromOrdenCompra]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[ordenCompra].[Descripcion] AS 'Descripcion',
	[ordenCompra].[Estado] AS 'Estado',
	[ordenCompra].[FechaCompra] AS 'FechaCompra',
	[ordenCompra].[IdEmpleado] AS 'IdEmpleado',
	[ordenCompra].[IdOrdenCompra] AS 'IdOrdenCompra',
	[ordenCompra].[IdOrdenPedido] AS 'IdOrdenPedido',
	[ordenCompra].[IdProveedor] AS 'IdProveedor'
FROM [dbo].[OrdenCompra] [ordenCompra]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetOrdenCompraByIdOrdenCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetOrdenCompraByIdOrdenCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetOrdenCompraByIdOrdenCompra] 
	@idOrdenCompra int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[ordenCompra].[Descripcion] AS 'Descripcion',
	[ordenCompra].[Estado] AS 'Estado',
	[ordenCompra].[FechaCompra] AS 'FechaCompra',
	[ordenCompra].[IdEmpleado] AS 'IdEmpleado',
	[ordenCompra].[IdOrdenCompra] AS 'IdOrdenCompra',
	[ordenCompra].[IdOrdenPedido] AS 'IdOrdenPedido',
	[ordenCompra].[IdProveedor] AS 'IdProveedor'
	FROM [dbo].[OrdenCompra] [ordenCompra]
	WHERE [IdOrdenCompra]=@idOrdenCompra

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetOrdenCompraByIdEmpleadoAndByIdOrdenPedidoAndByIdProveedor')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetOrdenCompraByIdEmpleadoAndByIdOrdenPedidoAndByIdProveedor] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetOrdenCompraByIdEmpleadoAndByIdOrdenPedidoAndByIdProveedor] 
	@idEmpleado smallint,
	@idOrdenPedido int,
	@idProveedor int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[ordenCompra].[Descripcion] AS 'Descripcion',
	[ordenCompra].[Estado] AS 'Estado',
	[ordenCompra].[FechaCompra] AS 'FechaCompra',
	[ordenCompra].[IdEmpleado] AS 'IdEmpleado',
	[ordenCompra].[IdOrdenCompra] AS 'IdOrdenCompra',
	[ordenCompra].[IdOrdenPedido] AS 'IdOrdenPedido',
	[ordenCompra].[IdProveedor] AS 'IdProveedor'
	FROM [dbo].[OrdenCompra] [ordenCompra]
	WHERE [IdEmpleado]=@idEmpleado AND [IdOrdenPedido]=@idOrdenPedido AND [IdProveedor]=@idProveedor

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[OrdenPedido] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertOrdenPedido]
    @descripcion nvarchar(200) = NULL,
	@estado bit,
	@fechaOrden datetime,
	@idEmpleado smallint,
	@idOrdenPedido int OUT,
	@idPedido int,
	@tipoOrdenPedido int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[OrdenPedido] ([Descripcion], [Estado], [FechaOrden], [IdEmpleado], [IdPedido], [TipoOrdenPedido])
	VALUES (@descripcion, @estado, @fechaOrden, @idEmpleado, @idPedido, @tipoOrdenPedido)
    SET @idOrdenPedido = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateOrdenPedido]
    @descripcion nvarchar(200) = NULL,
	@estado bit,
	@fechaOrden datetime,
	@idEmpleado smallint,
	@idOrdenPedido int,
	@idPedido int,
	@tipoOrdenPedido int
AS
BEGIN

	--The [dbo].[OrdenPedido] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[OrdenPedido] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [FechaOrden] = @fechaOrden, [IdEmpleado] = @idEmpleado, [IdPedido] = @idPedido, [TipoOrdenPedido] = @tipoOrdenPedido
	WHERE [IdOrdenPedido]=@idOrdenPedido

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteOrdenPedido]
	 @idOrdenPedido int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[OrdenPedido]
	WHERE [IdOrdenPedido]=@idOrdenPedido
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromOrdenPedido]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[ordenPedido].[Descripcion] AS 'Descripcion',
	[ordenPedido].[Estado] AS 'Estado',
	[ordenPedido].[FechaOrden] AS 'FechaOrden',
	[ordenPedido].[IdEmpleado] AS 'IdEmpleado',
	[ordenPedido].[IdOrdenPedido] AS 'IdOrdenPedido',
	[ordenPedido].[IdPedido] AS 'IdPedido',
	[ordenPedido].[TipoOrdenPedido] AS 'TipoOrdenPedido'
FROM [dbo].[OrdenPedido] [ordenPedido]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetOrdenPedidoByIdOrdenPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetOrdenPedidoByIdOrdenPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetOrdenPedidoByIdOrdenPedido] 
	@idOrdenPedido int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[ordenPedido].[Descripcion] AS 'Descripcion',
	[ordenPedido].[Estado] AS 'Estado',
	[ordenPedido].[FechaOrden] AS 'FechaOrden',
	[ordenPedido].[IdEmpleado] AS 'IdEmpleado',
	[ordenPedido].[IdOrdenPedido] AS 'IdOrdenPedido',
	[ordenPedido].[IdPedido] AS 'IdPedido',
	[ordenPedido].[TipoOrdenPedido] AS 'TipoOrdenPedido'
	FROM [dbo].[OrdenPedido] [ordenPedido]
	WHERE [IdOrdenPedido]=@idOrdenPedido

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetOrdenPedidoByIdEmpleadoAndByIdPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetOrdenPedidoByIdEmpleadoAndByIdPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetOrdenPedidoByIdEmpleadoAndByIdPedido] 
	@idEmpleado smallint,
	@idPedido int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[ordenPedido].[Descripcion] AS 'Descripcion',
	[ordenPedido].[Estado] AS 'Estado',
	[ordenPedido].[FechaOrden] AS 'FechaOrden',
	[ordenPedido].[IdEmpleado] AS 'IdEmpleado',
	[ordenPedido].[IdOrdenPedido] AS 'IdOrdenPedido',
	[ordenPedido].[IdPedido] AS 'IdPedido',
	[ordenPedido].[TipoOrdenPedido] AS 'TipoOrdenPedido'
	FROM [dbo].[OrdenPedido] [ordenPedido]
	WHERE [IdEmpleado]=@idEmpleado AND [IdPedido]=@idPedido

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Pedido] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertPedido]
    @descripcion nvarchar(200) = NULL,
	@estado bit,
	@fecha datetime,
	@idArea int,
	@idEmpleado smallint,
	@idPedido int OUT,
	@tipoPedido int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Pedido] ([Descripcion], [Estado], [Fecha], [IdArea], [IdEmpleado], [TipoPedido])
	VALUES (@descripcion, @estado, @fecha, @idArea, @idEmpleado, @tipoPedido)
    SET @idPedido = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdatePedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdatePedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdatePedido]
    @descripcion nvarchar(200) = NULL,
	@estado bit,
	@fecha datetime,
	@idArea int,
	@idEmpleado smallint,
	@idPedido int,
	@tipoPedido int
AS
BEGIN

	--The [dbo].[Pedido] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Pedido] 
	SET [Descripcion] = @descripcion, [Estado] = @estado, [Fecha] = @fecha, [IdArea] = @idArea, [IdEmpleado] = @idEmpleado, [TipoPedido] = @tipoPedido
	WHERE [IdPedido]=@idPedido

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeletePedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeletePedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeletePedido]
	 @idPedido int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Pedido]
	WHERE [IdPedido]=@idPedido
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromPedido]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[pedido].[Descripcion] AS 'Descripcion',
	[pedido].[Estado] AS 'Estado',
	[pedido].[Fecha] AS 'Fecha',
	[pedido].[IdArea] AS 'IdArea',
	[pedido].[IdEmpleado] AS 'IdEmpleado',
	[pedido].[IdPedido] AS 'IdPedido',
	[pedido].[TipoPedido] AS 'TipoPedido'
FROM [dbo].[Pedido] [pedido]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetPedidoByIdPedido')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetPedidoByIdPedido] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetPedidoByIdPedido] 
	@idPedido int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[pedido].[Descripcion] AS 'Descripcion',
	[pedido].[Estado] AS 'Estado',
	[pedido].[Fecha] AS 'Fecha',
	[pedido].[IdArea] AS 'IdArea',
	[pedido].[IdEmpleado] AS 'IdEmpleado',
	[pedido].[IdPedido] AS 'IdPedido',
	[pedido].[TipoPedido] AS 'TipoPedido'
	FROM [dbo].[Pedido] [pedido]
	WHERE [IdPedido]=@idPedido

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetPedidoByIdAreaAndByIdEmpleado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetPedidoByIdAreaAndByIdEmpleado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetPedidoByIdAreaAndByIdEmpleado] 
	@idArea int,
	@idEmpleado smallint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[pedido].[Descripcion] AS 'Descripcion',
	[pedido].[Estado] AS 'Estado',
	[pedido].[Fecha] AS 'Fecha',
	[pedido].[IdArea] AS 'IdArea',
	[pedido].[IdEmpleado] AS 'IdEmpleado',
	[pedido].[IdPedido] AS 'IdPedido',
	[pedido].[TipoPedido] AS 'TipoPedido'
	FROM [dbo].[Pedido] [pedido]
	WHERE [IdArea]=@idArea AND [IdEmpleado]=@idEmpleado

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Permiso] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertPermiso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertPermiso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertPermiso]
    @estado bit,
	@idEmpleado smallint,
	@idFormulario tinyint,
	@tipoPermiso tinyint
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Permiso] ([Estado], [IdEmpleado], [IdFormulario], [TipoPermiso])
	VALUES (@estado, @idEmpleado, @idFormulario, @tipoPermiso)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdatePermiso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdatePermiso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdatePermiso]
    @estado bit,
	@idEmpleado smallint,
	@idFormulario tinyint,
	@tipoPermiso tinyint
AS
BEGIN

	--The [dbo].[Permiso] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Permiso] 
	SET [Estado] = @estado
	WHERE [IdEmpleado]=@idEmpleado AND [IdFormulario]=@idFormulario AND [TipoPermiso]=@tipoPermiso

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeletePermiso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeletePermiso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeletePermiso]
	 @idEmpleado smallint,
	@idFormulario tinyint,
	@tipoPermiso tinyint
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Permiso]
	WHERE [IdEmpleado]=@idEmpleado AND [IdFormulario]=@idFormulario AND [TipoPermiso]=@tipoPermiso
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromPermiso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromPermiso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromPermiso]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[permiso].[Estado] AS 'Estado',
	[permiso].[IdEmpleado] AS 'IdEmpleado',
	[permiso].[IdFormulario] AS 'IdFormulario',
	[permiso].[TipoPermiso] AS 'TipoPermiso'
FROM [dbo].[Permiso] [permiso]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetPermisoByIdEmpleadoAndByIdFormularioAndByTipoPermiso')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetPermisoByIdEmpleadoAndByIdFormularioAndByTipoPermiso] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetPermisoByIdEmpleadoAndByIdFormularioAndByTipoPermiso] 
	@idEmpleado smallint,
	@idFormulario tinyint,
	@tipoPermiso tinyint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[permiso].[Estado] AS 'Estado',
	[permiso].[IdEmpleado] AS 'IdEmpleado',
	[permiso].[IdFormulario] AS 'IdFormulario',
	[permiso].[TipoPermiso] AS 'TipoPermiso'
	FROM [dbo].[Permiso] [permiso]
	WHERE [IdEmpleado]=@idEmpleado AND [IdFormulario]=@idFormulario AND [TipoPermiso]=@tipoPermiso

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Permiso] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[PlanCompra] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertPlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertPlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertPlanCompra]
    @descripcion nvarchar(200) = NULL,
	@idArea int,
	@idPlanCompra int OUT,
	@periodo char(4)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[PlanCompra] ([Descripcion], [IdArea], [Periodo])
	VALUES (@descripcion, @idArea, @periodo)
    SET @idPlanCompra = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdatePlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdatePlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdatePlanCompra]
    @descripcion nvarchar(200) = NULL,
	@idArea int,
	@idPlanCompra int,
	@periodo char(4)
AS
BEGIN

	--The [dbo].[PlanCompra] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[PlanCompra] 
	SET [Descripcion] = @descripcion, [IdArea] = @idArea, [Periodo] = @periodo
	WHERE [IdPlanCompra]=@idPlanCompra

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeletePlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeletePlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeletePlanCompra]
	 @idPlanCompra int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[PlanCompra]
	WHERE [IdPlanCompra]=@idPlanCompra
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromPlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromPlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromPlanCompra]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[planCompra].[Descripcion] AS 'Descripcion',
	[planCompra].[IdArea] AS 'IdArea',
	[planCompra].[IdPlanCompra] AS 'IdPlanCompra',
	[planCompra].[Periodo] AS 'Periodo'
FROM [dbo].[PlanCompra] [planCompra]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetPlanCompraByIdPlanCompra')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetPlanCompraByIdPlanCompra] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetPlanCompraByIdPlanCompra] 
	@idPlanCompra int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[planCompra].[Descripcion] AS 'Descripcion',
	[planCompra].[IdArea] AS 'IdArea',
	[planCompra].[IdPlanCompra] AS 'IdPlanCompra',
	[planCompra].[Periodo] AS 'Periodo'
	FROM [dbo].[PlanCompra] [planCompra]
	WHERE [IdPlanCompra]=@idPlanCompra

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetPlanCompraByIdArea')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetPlanCompraByIdArea] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetPlanCompraByIdArea] 
	@idArea int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[planCompra].[Descripcion] AS 'Descripcion',
	[planCompra].[IdArea] AS 'IdArea',
	[planCompra].[IdPlanCompra] AS 'IdPlanCompra',
	[planCompra].[Periodo] AS 'Periodo'
	FROM [dbo].[PlanCompra] [planCompra]
	WHERE [IdArea]=@idArea

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Proveedor] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertProveedor')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertProveedor] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertProveedor]
    @contacto nvarchar(100) = NULL,
	@direccion nvarchar(150) = NULL,
	@email nvarchar(50) = NULL,
	@idProveedor int OUT,
	@razonSocial nvarchar(50),
	@ruc char(11),
	@telefono nvarchar(15) = NULL,
	@tipoProveedor int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Proveedor] ([Contacto], [Direccion], [Email], [RazonSocial], [Ruc], [Telefono], [TipoProveedor])
	VALUES (@contacto, @direccion, @email, @razonSocial, @ruc, @telefono, @tipoProveedor)
    SET @idProveedor = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateProveedor')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateProveedor] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateProveedor]
    @contacto nvarchar(100) = NULL,
	@direccion nvarchar(150) = NULL,
	@email nvarchar(50) = NULL,
	@idProveedor int,
	@razonSocial nvarchar(50),
	@ruc char(11),
	@telefono nvarchar(15) = NULL,
	@tipoProveedor int
AS
BEGIN

	--The [dbo].[Proveedor] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Proveedor] 
	SET [Contacto] = @contacto, [Direccion] = @direccion, [Email] = @email, [RazonSocial] = @razonSocial, [Ruc] = @ruc, [Telefono] = @telefono, [TipoProveedor] = @tipoProveedor
	WHERE [IdProveedor]=@idProveedor

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteProveedor')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteProveedor] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteProveedor]
	 @idProveedor int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Proveedor]
	WHERE [IdProveedor]=@idProveedor
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromProveedor')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromProveedor] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromProveedor]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[proveedor].[Contacto] AS 'Contacto',
	[proveedor].[Direccion] AS 'Direccion',
	[proveedor].[Email] AS 'Email',
	[proveedor].[IdProveedor] AS 'IdProveedor',
	[proveedor].[RazonSocial] AS 'RazonSocial',
	[proveedor].[Ruc] AS 'Ruc',
	[proveedor].[Telefono] AS 'Telefono',
	[proveedor].[TipoProveedor] AS 'TipoProveedor'
FROM [dbo].[Proveedor] [proveedor]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetProveedorByIdProveedor')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetProveedorByIdProveedor] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetProveedorByIdProveedor] 
	@idProveedor int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[proveedor].[Contacto] AS 'Contacto',
	[proveedor].[Direccion] AS 'Direccion',
	[proveedor].[Email] AS 'Email',
	[proveedor].[IdProveedor] AS 'IdProveedor',
	[proveedor].[RazonSocial] AS 'RazonSocial',
	[proveedor].[Ruc] AS 'Ruc',
	[proveedor].[Telefono] AS 'Telefono',
	[proveedor].[TipoProveedor] AS 'TipoProveedor'
	FROM [dbo].[Proveedor] [proveedor]
	WHERE [IdProveedor]=@idProveedor

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Proveedor] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Subasta] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertSubasta]
    @descripcion nvarchar(200) = NULL,
	@fechaSubasta datetime,
	@hora varchar(10),
	@idSubasta int OUT,
	@lugar nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Subasta] ([Descripcion], [FechaSubasta], [Hora], [Lugar])
	VALUES (@descripcion, @fechaSubasta, @hora, @lugar)
    SET @idSubasta = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateSubasta]
    @descripcion nvarchar(200) = NULL,
	@fechaSubasta datetime,
	@hora varchar(10),
	@idSubasta int,
	@lugar nvarchar(50)
AS
BEGIN

	--The [dbo].[Subasta] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Subasta] 
	SET [Descripcion] = @descripcion, [FechaSubasta] = @fechaSubasta, [Hora] = @hora, [Lugar] = @lugar
	WHERE [IdSubasta]=@idSubasta

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteSubasta]
	 @idSubasta int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Subasta]
	WHERE [IdSubasta]=@idSubasta
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromSubasta]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[subasta].[Descripcion] AS 'Descripcion',
	[subasta].[FechaSubasta] AS 'FechaSubasta',
	[subasta].[Hora] AS 'Hora',
	[subasta].[IdSubasta] AS 'IdSubasta',
	[subasta].[Lugar] AS 'Lugar'
FROM [dbo].[Subasta] [subasta]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetSubastaByIdSubasta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetSubastaByIdSubasta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetSubastaByIdSubasta] 
	@idSubasta int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[subasta].[Descripcion] AS 'Descripcion',
	[subasta].[FechaSubasta] AS 'FechaSubasta',
	[subasta].[Hora] AS 'Hora',
	[subasta].[IdSubasta] AS 'IdSubasta',
	[subasta].[Lugar] AS 'Lugar'
	FROM [dbo].[Subasta] [subasta]
	WHERE [IdSubasta]=@idSubasta

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Subasta] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Sucursal] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertSucursal')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertSucursal] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertSucursal]
    @direccion nvarchar(50),
	@esPrincipal bit,
	@idSucursal int OUT,
	@idUbigeo int,
	@telefono nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Sucursal] ([Direccion], [EsPrincipal], [IdUbigeo], [Telefono])
	VALUES (@direccion, @esPrincipal, @idUbigeo, @telefono)
    SET @idSucursal = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateSucursal')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateSucursal] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateSucursal]
    @direccion nvarchar(50),
	@esPrincipal bit,
	@idSucursal int,
	@idUbigeo int,
	@telefono nvarchar(50)
AS
BEGIN

	--The [dbo].[Sucursal] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Sucursal] 
	SET [Direccion] = @direccion, [EsPrincipal] = @esPrincipal, [IdUbigeo] = @idUbigeo, [Telefono] = @telefono
	WHERE [IdSucursal]=@idSucursal

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteSucursal')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteSucursal] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteSucursal]
	 @idSucursal int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Sucursal]
	WHERE [IdSucursal]=@idSucursal
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromSucursal')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromSucursal] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromSucursal]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[sucursal].[Direccion] AS 'Direccion',
	[sucursal].[EsPrincipal] AS 'EsPrincipal',
	[sucursal].[IdSucursal] AS 'IdSucursal',
	[sucursal].[IdUbigeo] AS 'IdUbigeo',
	[sucursal].[Telefono] AS 'Telefono'
FROM [dbo].[Sucursal] [sucursal]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetSucursalByIdSucursal')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetSucursalByIdSucursal] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetSucursalByIdSucursal] 
	@idSucursal int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[sucursal].[Direccion] AS 'Direccion',
	[sucursal].[EsPrincipal] AS 'EsPrincipal',
	[sucursal].[IdSucursal] AS 'IdSucursal',
	[sucursal].[IdUbigeo] AS 'IdUbigeo',
	[sucursal].[Telefono] AS 'Telefono'
	FROM [dbo].[Sucursal] [sucursal]
	WHERE [IdSucursal]=@idSucursal

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetSucursalByIdUbigeo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetSucursalByIdUbigeo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetSucursalByIdUbigeo] 
	@idUbigeo int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[sucursal].[Direccion] AS 'Direccion',
	[sucursal].[EsPrincipal] AS 'EsPrincipal',
	[sucursal].[IdSucursal] AS 'IdSucursal',
	[sucursal].[IdUbigeo] AS 'IdUbigeo',
	[sucursal].[Telefono] AS 'Telefono'
	FROM [dbo].[Sucursal] [sucursal]
	WHERE [IdUbigeo]=@idUbigeo

	SET NOCOUNT OFF
END

GO

----------------------------------------------------------------
-- [dbo].[Telefono] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertTelefono')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertTelefono] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertTelefono]
    @idBien int,
	@marca nvarchar(50) = NULL,
	@modelo nvarchar(50),
	@nroCelular nvarchar(10),
	@tipoCelular nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Telefono] ([IdBien], [Marca], [Modelo], [NroCelular], [TipoCelular])
	VALUES (@idBien, @marca, @modelo, @nroCelular, @tipoCelular)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateTelefono')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateTelefono] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateTelefono]
    @idBien int,
	@marca nvarchar(50) = NULL,
	@modelo nvarchar(50),
	@nroCelular nvarchar(10),
	@tipoCelular nvarchar(50)
AS
BEGIN

	--The [dbo].[Telefono] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Telefono] 
	SET [Marca] = @marca, [Modelo] = @modelo, [NroCelular] = @nroCelular, [TipoCelular] = @tipoCelular
	WHERE [IdBien]=@idBien

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteTelefono')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteTelefono] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteTelefono]
	 @idBien int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Telefono]
	WHERE [IdBien]=@idBien
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromTelefono')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromTelefono] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromTelefono]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[telefono].[IdBien] AS 'IdBien',
	[telefono].[Marca] AS 'Marca',
	[telefono].[Modelo] AS 'Modelo',
	[telefono].[NroCelular] AS 'NroCelular',
	[telefono].[TipoCelular] AS 'TipoCelular'
FROM [dbo].[Telefono] [telefono]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetTelefonoByIdBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetTelefonoByIdBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetTelefonoByIdBien] 
	@idBien int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[telefono].[IdBien] AS 'IdBien',
	[telefono].[Marca] AS 'Marca',
	[telefono].[Modelo] AS 'Modelo',
	[telefono].[NroCelular] AS 'NroCelular',
	[telefono].[TipoCelular] AS 'TipoCelular'
	FROM [dbo].[Telefono] [telefono]
	WHERE [IdBien]=@idBien

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Telefono] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Ubigeo] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertUbigeo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertUbigeo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertUbigeo]
    @departamento varchar(100) = NULL,
	@distrito varchar(100) = NULL,
	@idDepartamento int = NULL,
	@idDistrito int = NULL,
	@idProvincia int = NULL,
	@idUbigeo int,
	@provincia varchar(100) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Ubigeo] ([Departamento], [Distrito], [IdDepartamento], [IdDistrito], [IdProvincia], [IdUbigeo], [Provincia])
	VALUES (@departamento, @distrito, @idDepartamento, @idDistrito, @idProvincia, @idUbigeo, @provincia)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateUbigeo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateUbigeo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateUbigeo]
    @departamento varchar(100) = NULL,
	@distrito varchar(100) = NULL,
	@idDepartamento int = NULL,
	@idDistrito int = NULL,
	@idProvincia int = NULL,
	@idUbigeo int,
	@provincia varchar(100) = NULL
AS
BEGIN

	--The [dbo].[Ubigeo] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Ubigeo] 
	SET [Departamento] = @departamento, [Distrito] = @distrito, [IdDepartamento] = @idDepartamento, [IdDistrito] = @idDistrito, [IdProvincia] = @idProvincia, [Provincia] = @provincia
	WHERE [IdUbigeo]=@idUbigeo

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteUbigeo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteUbigeo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteUbigeo]
	 @idUbigeo int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Ubigeo]
	WHERE [IdUbigeo]=@idUbigeo
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromUbigeo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromUbigeo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromUbigeo]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[ubigeo].[Departamento] AS 'Departamento',
	[ubigeo].[Distrito] AS 'Distrito',
	[ubigeo].[IdDepartamento] AS 'IdDepartamento',
	[ubigeo].[IdDistrito] AS 'IdDistrito',
	[ubigeo].[IdProvincia] AS 'IdProvincia',
	[ubigeo].[IdUbigeo] AS 'IdUbigeo',
	[ubigeo].[Provincia] AS 'Provincia'
FROM [dbo].[Ubigeo] [ubigeo]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetUbigeoByIdUbigeo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetUbigeoByIdUbigeo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetUbigeoByIdUbigeo] 
	@idUbigeo int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[ubigeo].[Departamento] AS 'Departamento',
	[ubigeo].[Distrito] AS 'Distrito',
	[ubigeo].[IdDepartamento] AS 'IdDepartamento',
	[ubigeo].[IdDistrito] AS 'IdDistrito',
	[ubigeo].[IdProvincia] AS 'IdProvincia',
	[ubigeo].[IdUbigeo] AS 'IdUbigeo',
	[ubigeo].[Provincia] AS 'Provincia'
	FROM [dbo].[Ubigeo] [ubigeo]
	WHERE [IdUbigeo]=@idUbigeo

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Ubigeo] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[UnidadMedidia] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertUnidadMedidia')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertUnidadMedidia] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertUnidadMedidia]
    @abreviatura nvarchar(10),
	@descripcion nvarchar(20) = NULL,
	@esUnidadBase bit,
	@idUnidadMedida int OUT,
	@nombre nvarchar(50)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[UnidadMedidia] ([Abreviatura], [Descripcion], [EsUnidadBase], [Nombre])
	VALUES (@abreviatura, @descripcion, @esUnidadBase, @nombre)
    SET @idUnidadMedida = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateUnidadMedidia')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateUnidadMedidia] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateUnidadMedidia]
    @abreviatura nvarchar(10),
	@descripcion nvarchar(20) = NULL,
	@esUnidadBase bit,
	@idUnidadMedida int,
	@nombre nvarchar(50)
AS
BEGIN

	--The [dbo].[UnidadMedidia] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[UnidadMedidia] 
	SET [Abreviatura] = @abreviatura, [Descripcion] = @descripcion, [EsUnidadBase] = @esUnidadBase, [Nombre] = @nombre
	WHERE [IdUnidadMedida]=@idUnidadMedida

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteUnidadMedidia')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteUnidadMedidia] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteUnidadMedidia]
	 @idUnidadMedida int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[UnidadMedidia]
	WHERE [IdUnidadMedida]=@idUnidadMedida
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromUnidadMedidia')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromUnidadMedidia] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromUnidadMedidia]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[unidadMedidia].[Abreviatura] AS 'Abreviatura',
	[unidadMedidia].[Descripcion] AS 'Descripcion',
	[unidadMedidia].[EsUnidadBase] AS 'EsUnidadBase',
	[unidadMedidia].[IdUnidadMedida] AS 'IdUnidadMedida',
	[unidadMedidia].[Nombre] AS 'Nombre'
FROM [dbo].[UnidadMedidia] [unidadMedidia]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetUnidadMedidiaByIdUnidadMedida')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetUnidadMedidiaByIdUnidadMedida] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetUnidadMedidiaByIdUnidadMedida] 
	@idUnidadMedida int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[unidadMedidia].[Abreviatura] AS 'Abreviatura',
	[unidadMedidia].[Descripcion] AS 'Descripcion',
	[unidadMedidia].[EsUnidadBase] AS 'EsUnidadBase',
	[unidadMedidia].[IdUnidadMedida] AS 'IdUnidadMedida',
	[unidadMedidia].[Nombre] AS 'Nombre'
	FROM [dbo].[UnidadMedidia] [unidadMedidia]
	WHERE [IdUnidadMedida]=@idUnidadMedida

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[UnidadMedidia] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Usuario] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertUsuario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertUsuario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertUsuario]
    @esAdministrador bit,
	@fechaRegistro datetime,
	@idEmpleado smallint,
	@password nvarchar(10),
	@username nvarchar(10)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Usuario] ([EsAdministrador], [FechaRegistro], [IdEmpleado], [Password], [Username])
	VALUES (@esAdministrador, @fechaRegistro, @idEmpleado, @password, @username)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateUsuario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateUsuario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateUsuario]
    @esAdministrador bit,
	@fechaRegistro datetime,
	@idEmpleado smallint,
	@password nvarchar(10),
	@username nvarchar(10)
AS
BEGIN

	--The [dbo].[Usuario] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Usuario] 
	SET [EsAdministrador] = @esAdministrador, [FechaRegistro] = @fechaRegistro, [Password] = @password, [Username] = @username
	WHERE [IdEmpleado]=@idEmpleado

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteUsuario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteUsuario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteUsuario]
	 @idEmpleado smallint
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Usuario]
	WHERE [IdEmpleado]=@idEmpleado
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromUsuario')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromUsuario] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromUsuario]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[usuario].[EsAdministrador] AS 'EsAdministrador',
	[usuario].[FechaRegistro] AS 'FechaRegistro',
	[usuario].[IdEmpleado] AS 'IdEmpleado',
	[usuario].[Password] AS 'Password',
	[usuario].[Username] AS 'Username'
FROM [dbo].[Usuario] [usuario]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetUsuarioByIdEmpleado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetUsuarioByIdEmpleado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetUsuarioByIdEmpleado] 
	@idEmpleado smallint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[usuario].[EsAdministrador] AS 'EsAdministrador',
	[usuario].[FechaRegistro] AS 'FechaRegistro',
	[usuario].[IdEmpleado] AS 'IdEmpleado',
	[usuario].[Password] AS 'Password',
	[usuario].[Username] AS 'Username'
	FROM [dbo].[Usuario] [usuario]
	WHERE [IdEmpleado]=@idEmpleado

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Usuario] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Vehiculo] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertVehiculo]
    @idBien int,
	@marca nvarchar(50) = NULL,
	@modelo nvarchar(50),
	@nroPlaca nvarchar(10),
	@tipoVehiculo int
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Vehiculo] ([IdBien], [Marca], [Modelo], [NroPlaca], [TipoVehiculo])
	VALUES (@idBien, @marca, @modelo, @nroPlaca, @tipoVehiculo)
    
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateVehiculo]
    @idBien int,
	@marca nvarchar(50) = NULL,
	@modelo nvarchar(50),
	@nroPlaca nvarchar(10),
	@tipoVehiculo int
AS
BEGIN

	--The [dbo].[Vehiculo] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Vehiculo] 
	SET [Marca] = @marca, [Modelo] = @modelo, [NroPlaca] = @nroPlaca, [TipoVehiculo] = @tipoVehiculo
	WHERE [IdBien]=@idBien

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteVehiculo]
	 @idBien int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Vehiculo]
	WHERE [IdBien]=@idBien
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromVehiculo')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromVehiculo] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromVehiculo]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[vehiculo].[IdBien] AS 'IdBien',
	[vehiculo].[Marca] AS 'Marca',
	[vehiculo].[Modelo] AS 'Modelo',
	[vehiculo].[NroPlaca] AS 'NroPlaca',
	[vehiculo].[TipoVehiculo] AS 'TipoVehiculo'
FROM [dbo].[Vehiculo] [vehiculo]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetVehiculoByIdBien')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetVehiculoByIdBien] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetVehiculoByIdBien] 
	@idBien int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[vehiculo].[IdBien] AS 'IdBien',
	[vehiculo].[Marca] AS 'Marca',
	[vehiculo].[Modelo] AS 'Modelo',
	[vehiculo].[NroPlaca] AS 'NroPlaca',
	[vehiculo].[TipoVehiculo] AS 'TipoVehiculo'
	FROM [dbo].[Vehiculo] [vehiculo]
	WHERE [IdBien]=@idBien

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[Vehiculo] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[VehiculoAlquilado] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertVehiculoAlquilado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertVehiculoAlquilado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertVehiculoAlquilado]
    @contrato nvarchar(250) = NULL,
	@idBien int,
	@idVehiculoAlquilado int OUT,
	@montoPago money
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[VehiculoAlquilado] ([Contrato], [IdBien], [MontoPago])
	VALUES (@contrato, @idBien, @montoPago)
    SET @idVehiculoAlquilado = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateVehiculoAlquilado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateVehiculoAlquilado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateVehiculoAlquilado]
    @contrato nvarchar(250) = NULL,
	@idBien int,
	@idVehiculoAlquilado int,
	@montoPago money
AS
BEGIN

	--The [dbo].[VehiculoAlquilado] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[VehiculoAlquilado] 
	SET [Contrato] = @contrato, [MontoPago] = @montoPago
	WHERE [IdBien]=@idBien AND [IdVehiculoAlquilado]=@idVehiculoAlquilado

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteVehiculoAlquilado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteVehiculoAlquilado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteVehiculoAlquilado]
	 @idBien int,
	@idVehiculoAlquilado int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[VehiculoAlquilado]
	WHERE [IdBien]=@idBien AND [IdVehiculoAlquilado]=@idVehiculoAlquilado
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromVehiculoAlquilado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromVehiculoAlquilado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromVehiculoAlquilado]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[vehiculoAlquilado].[Contrato] AS 'Contrato',
	[vehiculoAlquilado].[IdBien] AS 'IdBien',
	[vehiculoAlquilado].[IdVehiculoAlquilado] AS 'IdVehiculoAlquilado',
	[vehiculoAlquilado].[MontoPago] AS 'MontoPago'
FROM [dbo].[VehiculoAlquilado] [vehiculoAlquilado]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetVehiculoAlquiladoByIdBienAndByIdVehiculoAlquilado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetVehiculoAlquiladoByIdBienAndByIdVehiculoAlquilado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetVehiculoAlquiladoByIdBienAndByIdVehiculoAlquilado] 
	@idBien int,
	@idVehiculoAlquilado int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[vehiculoAlquilado].[Contrato] AS 'Contrato',
	[vehiculoAlquilado].[IdBien] AS 'IdBien',
	[vehiculoAlquilado].[IdVehiculoAlquilado] AS 'IdVehiculoAlquilado',
	[vehiculoAlquilado].[MontoPago] AS 'MontoPago'
	FROM [dbo].[VehiculoAlquilado] [vehiculoAlquilado]
	WHERE [IdBien]=@idBien AND [IdVehiculoAlquilado]=@idVehiculoAlquilado

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[VehiculoAlquilado] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[VehiculoPropio] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertVehiculoPropio')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertVehiculoPropio] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertVehiculoPropio]
    @fechaCompra datetime,
	@idBien int,
	@idVehiculoPropio int OUT,
	@precio money,
	@tarjetaPropiedad nvarchar(50) = NULL
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[VehiculoPropio] ([FechaCompra], [IdBien], [Precio], [TarjetaPropiedad])
	VALUES (@fechaCompra, @idBien, @precio, @tarjetaPropiedad)
    SET @idVehiculoPropio = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateVehiculoPropio')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateVehiculoPropio] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateVehiculoPropio]
    @fechaCompra datetime,
	@idBien int,
	@idVehiculoPropio int,
	@precio money,
	@tarjetaPropiedad nvarchar(50) = NULL
AS
BEGIN

	--The [dbo].[VehiculoPropio] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[VehiculoPropio] 
	SET [FechaCompra] = @fechaCompra, [Precio] = @precio, [TarjetaPropiedad] = @tarjetaPropiedad
	WHERE [IdBien]=@idBien AND [IdVehiculoPropio]=@idVehiculoPropio

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteVehiculoPropio')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteVehiculoPropio] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteVehiculoPropio]
	 @idBien int,
	@idVehiculoPropio int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[VehiculoPropio]
	WHERE [IdBien]=@idBien AND [IdVehiculoPropio]=@idVehiculoPropio
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromVehiculoPropio')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromVehiculoPropio] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromVehiculoPropio]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[vehiculoPropio].[FechaCompra] AS 'FechaCompra',
	[vehiculoPropio].[IdBien] AS 'IdBien',
	[vehiculoPropio].[IdVehiculoPropio] AS 'IdVehiculoPropio',
	[vehiculoPropio].[Precio] AS 'Precio',
	[vehiculoPropio].[TarjetaPropiedad] AS 'TarjetaPropiedad'
FROM [dbo].[VehiculoPropio] [vehiculoPropio]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetVehiculoPropioByIdBienAndByIdVehiculoPropio')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetVehiculoPropioByIdBienAndByIdVehiculoPropio] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetVehiculoPropioByIdBienAndByIdVehiculoPropio] 
	@idBien int,
	@idVehiculoPropio int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[vehiculoPropio].[FechaCompra] AS 'FechaCompra',
	[vehiculoPropio].[IdBien] AS 'IdBien',
	[vehiculoPropio].[IdVehiculoPropio] AS 'IdVehiculoPropio',
	[vehiculoPropio].[Precio] AS 'Precio',
	[vehiculoPropio].[TarjetaPropiedad] AS 'TarjetaPropiedad'
	FROM [dbo].[VehiculoPropio] [vehiculoPropio]
	WHERE [IdBien]=@idBien AND [IdVehiculoPropio]=@idVehiculoPropio

	SET NOCOUNT OFF
END

GO


--The GetByFK stored procedure cannot be created because the [dbo].[VehiculoPropio] table doesn't have at least one foreign key column or the foreign keys are also primary keys.

----------------------------------------------------------------
-- [dbo].[Venta] Table
--
IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'InsertVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[InsertVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[InsertVenta]
    @descripcion nvarchar(200) = NULL,
	@fechaVenta datetime,
	@idCliente int,
	@idEmpleado smallint,
	@idVenta int OUT,
	@numero char(7),
	@serie char(4)
AS
BEGIN
	SET NOCOUNT ON
	
	BEGIN TRY
    INSERT INTO [dbo].[Venta] ([Descripcion], [FechaVenta], [IdCliente], [IdEmpleado], [Numero], [Serie])
	VALUES (@descripcion, @fechaVenta, @idCliente, @idEmpleado, @numero, @serie)
    SET @idVenta = SCOPE_IDENTITY()
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH
    
    SET NOCOUNT OFF
END    

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'UpdateVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[UpdateVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[UpdateVenta]
    @descripcion nvarchar(200) = NULL,
	@fechaVenta datetime,
	@idCliente int,
	@idEmpleado smallint,
	@idVenta int,
	@numero char(7),
	@serie char(4)
AS
BEGIN

	--The [dbo].[Venta] table doesn't have a timestamp column. Optimistic concurrency logic cannot be generated
	SET NOCOUNT ON

	BEGIN TRY
	UPDATE [dbo].[Venta] 
	SET [Descripcion] = @descripcion, [FechaVenta] = @fechaVenta, [IdCliente] = @idCliente, [IdEmpleado] = @idEmpleado, [Numero] = @numero, [Serie] = @serie
	WHERE [IdVenta]=@idVenta

	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY

    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'DeleteVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[DeleteVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[DeleteVenta]
	 @idVenta int
AS
BEGIN
	SET NOCOUNT ON
	
    DELETE FROM [dbo].[Venta]
	WHERE [IdVenta]=@idVenta
    
    SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetAllFromVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetAllFromVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetAllFromVenta]    
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT
	[venta].[Descripcion] AS 'Descripcion',
	[venta].[FechaVenta] AS 'FechaVenta',
	[venta].[IdCliente] AS 'IdCliente',
	[venta].[IdEmpleado] AS 'IdEmpleado',
	[venta].[IdVenta] AS 'IdVenta',
	[venta].[Numero] AS 'Numero',
	[venta].[Serie] AS 'Serie'
FROM [dbo].[Venta] [venta]

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetVentaByIdVenta')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetVentaByIdVenta] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetVentaByIdVenta] 
	@idVenta int
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[venta].[Descripcion] AS 'Descripcion',
	[venta].[FechaVenta] AS 'FechaVenta',
	[venta].[IdCliente] AS 'IdCliente',
	[venta].[IdEmpleado] AS 'IdEmpleado',
	[venta].[IdVenta] AS 'IdVenta',
	[venta].[Numero] AS 'Numero',
	[venta].[Serie] AS 'Serie'
	FROM [dbo].[Venta] [venta]
	WHERE [IdVenta]=@idVenta

	SET NOCOUNT OFF
END

GO

IF NOT EXISTS (SELECT NAME FROM sys.objects WHERE TYPE = 'P' AND NAME = 'GetVentaByIdClienteAndByIdEmpleado')
BEGIN
	EXEC('CREATE PROCEDURE [dbo].[GetVentaByIdClienteAndByIdEmpleado] AS RETURN')
END

GO

ALTER PROCEDURE [dbo].[GetVentaByIdClienteAndByIdEmpleado] 
	@idCliente int,
	@idEmpleado smallint
AS
BEGIN

	SET NOCOUNT ON
	
	SELECT
	[venta].[Descripcion] AS 'Descripcion',
	[venta].[FechaVenta] AS 'FechaVenta',
	[venta].[IdCliente] AS 'IdCliente',
	[venta].[IdEmpleado] AS 'IdEmpleado',
	[venta].[IdVenta] AS 'IdVenta',
	[venta].[Numero] AS 'Numero',
	[venta].[Serie] AS 'Serie'
	FROM [dbo].[Venta] [venta]
	WHERE [IdCliente]=@idCliente AND [IdEmpleado]=@idEmpleado

	SET NOCOUNT OFF
END

GO


